Excel IF Function 1

[naiku]

Hi All

Not sure how to explain this but I have 2 areas on my sheet, one displaying names and each name has a quantity, then on another area I have 2 empty cells the user types in a name into the first cell and the second is automatically completed using data taken from the table with the quantities.

I have 12 names and can use an IF statement for the first 8, but after that for some reason I cannot enter any more. The statement I am using is IF(B10=B18,C18,IF(B10=B19,C19,0)) etc for each cell up to B25 and this all works fine, but when I try to go on from B26 to B29 I can't add any more to my nested IF statement, I read someplace that I can only have 7 statements (I think) in a nested IF.

So how can I change this so that it works for all entries on the table? is there a completely different way that I should be doing this? or is using the IF statement the way to go. Hope that's explained with some degree of sense.

Thanks

 

[merlinx]

Macros & VBA Functions are the clean way to do this. If I understood this correctly, then your writing this in the work sheet cell directly. So you could use another area for each calculation, for example cell G10 : IF(B1 = B2,0,1), then at the end of this column say for G10 to G26 then G27 : IF((Add(G10;G26)=0),"True","False&quot, as to the command Add : I'm translating this from German so it may be another terminolgy. As such G27 has your answer.

 

[DaleWatson123321]

Hi naiku,

Based on your situation, I would recommend the use of the VLOOKUP function.

Here are the steps to follow...

1) Assign a range name to the area containing the names and quantity. You can use whatever name you like. In the formula below, I've used the name "tbl".

2) For the "2 empty cells" area, assign another range name of your choice to the cell where the user will enter the name. I've used the name "input".

3) In the other cell (for quantity), enter the following formula...

=VLOOKUP(input,tbl,2,FALSE)

Method of Assigning a Range Name:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[naiku]

Thanks Dale, that works exactly as I wanted. I had thought about using VLOOKUP, but have not used it before and was unsure if it would work on this.

Thanks again

Naiku