Go Back  British Expats > Living & Moving Abroad > Middle East > The Sand Pit
Reload this Page >

Any MS Excel experts on the board?

Any MS Excel experts on the board?

Old Feb 18th 2014, 12:43 pm
  #1  
BE Forum Addict
Thread Starter
 
Joined: Sep 2009
Location: Abu Dhabi
Posts: 1,800
Brains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond repute
Default Any MS Excel experts on the board?

stuck with a problem that google hasn't managed to help me fix yet. Anyone know MS Excel?

I'm 99% through the template I'm creating, however there is one cell I am stumped with. What needs to be displayed is the result of 2 drop down menus further up the template

based on my little table attached, if I have selected "50 Kg" from one drop down menu, and "Singapore" from the other one, what do I put in the cell so I am working on to so it knows to display "110"?
Attached Thumbnails Any MS Excel experts on the board?-calculate-capture.jpg  
Brains1983 is offline  
Old Feb 18th 2014, 1:02 pm
  #2  
Just Joined
 
CanuckGrl's Avatar
 
Joined: Feb 2014
Posts: 7
CanuckGrl is an unknown quantity at this point
Default Re: Any MS Excel experts on the board?

I don't entirely understand the question. Are you looking for a formula to be used in more than one cell that will give the value of, for example, 50kg and Singapore, 500kg and Dubai, etc., the answer of which will change based on different inputs?

If you simply want the "110" to appear in another cell, you just need to tell it to copy/link the value of whatever that cell number is.
CanuckGrl is offline  
Old Feb 18th 2014, 1:04 pm
  #3  
BE Forum Addict
Thread Starter
 
Joined: Sep 2009
Location: Abu Dhabi
Posts: 1,800
Brains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Originally Posted by CanuckGrl View Post
I don't entirely understand the question. Are you looking for a formula to be used in more than one cell that will give the value of, for example, 50kg and Singapore, 500kg and Dubai, etc., the answer of which will change based on different inputs?

If you simply want the "110" to appear in another cell, you just need to tell it to copy/link the value of whatever that cell number is.
Based on what two options the user has selected in the two drop downs (one will be the city, one will be the weight), I want the cell I am having trouble with tto show the number where they meet on the table I attached.
Brains1983 is offline  
Old Feb 18th 2014, 4:30 pm
  #4  
BE Enthusiast
 
csdf's Avatar
 
Joined: Jun 2010
Location: Abu Dhabi
Posts: 807
csdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond reputecsdf has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

If the user selection city is in cell A1 and weight selection in A2, your city names are in cells b10:f10 and your weights are in cells A11:A14 then the formula would be:
=HLOOKUP(A1,$B$10:$F$14,MATCH(A2,$A$11:$A$14)+1,FA LSE)

The FALSE statement should be a complete word - not sure why it's being broken up.

For this to work you need to make sure that the user weight selection choices match exactly, character for character, the list in cells A11:A14. You can do this using Data Validation and a list.

Last edited by csdf; Feb 18th 2014 at 4:40 pm.
csdf is offline  
Old Feb 18th 2014, 4:32 pm
  #5  
womble
 
Joined: Sep 2005
Posts: 2,605
OriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Originally Posted by Brains1983 View Post
stuck with a problem that google hasn't managed to help me fix yet. Anyone know MS Excel?

I'm 99% through the template I'm creating, however there is one cell I am stumped with. What needs to be displayed is the result of 2 drop down menus further up the template

based on my little table attached, if I have selected "50 Kg" from one drop down menu, and "Singapore" from the other one, what do I put in the cell so I am working on to so it knows to display "110"?
you will need to write nested AND IF statements
OriginalSunshine is offline  
Old Feb 18th 2014, 4:34 pm
  #6  
BE Forum Addict
Thread Starter
 
Joined: Sep 2009
Location: Abu Dhabi
Posts: 1,800
Brains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Originally Posted by OriginalSunshine View Post
you will need to write nested AND IF statements
Originally Posted by csdf View Post
I would use either an hlookup, or an offset using the index and match functions.
I wish I understood this
Brains1983 is offline  
Old Feb 18th 2014, 4:37 pm
  #7  
womble
 
Joined: Sep 2005
Posts: 2,605
OriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

then you need to learn to do it

teach yourself Brains

go to the help function, look for HLOOKUP or ANDIF in the button that says "function help" and follow the instructions
OriginalSunshine is offline  
Old Feb 18th 2014, 6:49 pm
  #8  
Forum Regular
 
Joined: Apr 2010
Location: Al Ain, UAE
Posts: 54
Casta is just really niceCasta is just really niceCasta is just really niceCasta is just really niceCasta is just really niceCasta is just really niceCasta is just really niceCasta is just really niceCasta is just really niceCasta is just really nice
Default Re: Any MS Excel experts on the board?

First of all I have to agree with the poster who said you had to learn. I can't emphasise enough how practice helps learn Excel. Nothing like learning from doing. Google often comes up with great links although admittedly sometimes it helps to know the function you'll need which isn't always easily known

Couple of links to help you understand how it works:
http://www.mrexcel.com/articles/exce...ndex-match.php
http://tipsindeed.com/excel-function...-in-excel.html

I've put the answer in an attached pic. A9 and B8 are Data Validation drop down lists so the answer in B9 changes when they do.

Good luck
Attached Thumbnails Any MS Excel experts on the board?-picture-1.png  
Casta is offline  
Old Feb 19th 2014, 2:26 am
  #9  
womble
 
Joined: Sep 2005
Posts: 2,605
OriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Casta, thanks! I learnt something new today
I've never used Index/Match before.
OriginalSunshine is offline  
Old Feb 19th 2014, 5:45 am
  #10  
BE Forum Addict
Thread Starter
 
Joined: Sep 2009
Location: Abu Dhabi
Posts: 1,800
Brains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Originally Posted by Casta View Post
First of all I have to agree with the poster who said you had to learn. I can't emphasise enough how practice helps learn Excel. Nothing like learning from doing. Google often comes up with great links although admittedly sometimes it helps to know the function you'll need which isn't always easily known

Couple of links to help you understand how it works:
http://www.mrexcel.com/articles/exce...ndex-match.php
http://tipsindeed.com/excel-function...-in-excel.html

I've put the answer in an attached pic. A9 and B8 are Data Validation drop down lists so the answer in B9 changes when they do.

Good luck

That worked perfectly, thank you very much. Karma Sent!
Brains1983 is offline  
Old Feb 19th 2014, 6:55 am
  #11  
**** it we'll do it live
 
shiva's Avatar
 
Joined: Oct 2004
Location: Dubai
Posts: 7,892
shiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond reputeshiva has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

$5 to fix excel

go here
shiva is offline  
Old Feb 19th 2014, 7:00 am
  #12  
BE Forum Addict
Thread Starter
 
Joined: Sep 2009
Location: Abu Dhabi
Posts: 1,800
Brains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond reputeBrains1983 has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Originally Posted by shiva View Post
$5 to fix excel

go here
there's a dude on there that does a simpsons style caricature for you aswell.
Brains1983 is offline  
Old Feb 19th 2014, 7:03 am
  #13  
Millhouse is not a meme
 
Millhouse's Avatar
 
Joined: Aug 2009
Location: Disneyland, Dubai
Posts: 14,765
Millhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond reputeMillhouse has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Originally Posted by OriginalSunshine View Post
you will need to write nested AND IF statements
I would suggest that you shouldn't be writing nested IF statements - much better ways of coming to the same answer (index and match combo).

In a former life I used to teach xhell. My advise was always to get rid of IF, AND, OR statements - they are confusing
Millhouse is offline  
Old Feb 19th 2014, 11:33 am
  #14  
womble
 
Joined: Sep 2005
Posts: 2,605
OriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond reputeOriginalSunshine has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

Originally Posted by Millhouse View Post
I would suggest that you shouldn't be writing nested IF statements - much better ways of coming to the same answer (index and match combo).

In a former life I used to teach xhell. My advise was always to get rid of IF, AND, OR statements - they are confusing
Very true. But I come from an ex Fortran 77 background, no index/match then, so I'm just being lazy since I can write nests easily
OriginalSunshine is offline  
Old Feb 19th 2014, 6:49 pm
  #15  
"Insert witty title here"
 
Irishbeekeeper's Avatar
 
Joined: Nov 2012
Location: dxb
Posts: 2,232
Irishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond reputeIrishbeekeeper has a reputation beyond repute
Default Re: Any MS Excel experts on the board?

I like turtles!!
Irishbeekeeper is offline  

Contact Us Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright © 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.