IF statements

Y

y_not

I am using a formula which relies on the user selecting an option form
drop down list. Depending on thier choice the sheet displays informatio
relative to the selection.

This has worked fine for a while but I have been asked to add two extr
items to the drop down list. The effect of this that Excel reports a
error because (I think) there are too many IF statements.

Does anyone have any idea of how I can get around this, please?

If the formula makes any sense I have added it below: -

=IF(Chose="Birkenhead",B70,IF(Chose="Bradford",B123,IF(Chose="Derby",B176,IF(Chose="Leeds",B229,IF(Chose="Rochdale",B282,IF(Chose="Sainsbury's",B335,IF(Chose="Skelmersdale",B388,IF(Chose="Basingstoke",B441,'C:\Document
an
Settings\Desktop\Test\Tracker\[Skelmersdale.xls]Driver'!B8+'C:\Document
an
Settings\Desktop\Test\Tracker\[Sainsbury''s.xls]Driver'!B8+'C:\Document
and Settings\Desktop\Test\Tracker\[Rochdale.xls]Driver'!B8+'C:\Document
and Settings\Desktop\Test\Tracker\[Leeds.xls]Driver'!B8+'C:\Document
an
Settings\Desktop\Test\Tracker\[Bradford.xls]Driver'!B8+'C:\Document
an
Settings\Desktop\Test\Tracker\[Birkenhead.xls]Driver'!B8+'C:\Document
and Settings\Desktop\Test\Tracker\[Derby.xls]Driver'!B8))))))))

The list is of town names, "Chose" is the named cell of the drop dow
and "Region" simply totals the data for the eight town
 
D

Dave Peterson

Since you're adding the values, maybe you could use:

IF(Chose="Birkenhead",B70,0) + IF(Chose="Bradford",B123,0) + ...

Then you don't have as many nested if's.



y_not said:
I am using a formula which relies on the user selecting an option form a
drop down list. Depending on thier choice the sheet displays information
relative to the selection.

This has worked fine for a while but I have been asked to add two extra
items to the drop down list. The effect of this that Excel reports an
error because (I think) there are too many IF statements.

Does anyone have any idea of how I can get around this, please?

If the formula makes any sense I have added it below: -

=IF(Chose="Birkenhead",B70,IF(Chose="Bradford",B123,IF(Chose="Derby",B176,IF(Chose="Leeds",B229,IF(Chose="Rochdale",B282,IF(Chose="Sainsbury's",B335,IF(Chose="Skelmersdale",B388,IF(Chose="Basingstoke",B441,'C:\Documents
and
Settings\Desktop\Test\Tracker\[Skelmersdale.xls]Driver'!B8+'C:\Documents
and
Settings\Desktop\Test\Tracker\[Sainsbury''s.xls]Driver'!B8+'C:\Documents
and Settings\Desktop\Test\Tracker\[Rochdale.xls]Driver'!B8+'C:\Documents
and Settings\Desktop\Test\Tracker\[Leeds.xls]Driver'!B8+'C:\Documents
and
Settings\Desktop\Test\Tracker\[Bradford.xls]Driver'!B8+'C:\Documents
and
Settings\Desktop\Test\Tracker\[Birkenhead.xls]Driver'!B8+'C:\Documents
and Settings\Desktop\Test\Tracker\[Derby.xls]Driver'!B8))))))))

The list is of town names, "Chose" is the named cell of the drop down
and "Region" simply totals the data for the eight towns
Click to expand...
 
D

Domenic

Let A1:A8 contain the following...

Birkenhead
Bradford
Derby
Leeds
Rochdale
Sainsbury
Skelmersdale
Basingstoke


C1:

=IF(ISNUMBER(MATCH(Chose,A1:A8,0)),INDEX(B70:B441,MATCH(Chose,A1:A8,0)*53-53+1),"")


D1:

=IF(C1<>"","",SUMPRODUCT(N(INDIRECT("'["&A1:A7&".xls]Driver'!B8"))))

OR

=IF(C1<>"","",SUMPRODUCT(N(INDIRECT("'C:\Documents an
Settings\Desktop\Test\Tracker\["&A1:A7&".xls]Driver'!B8"))))

Note that the range A1:A7 does not include a reference to A8. Also
note that referenced workbooks need to be opened for the formula t
work. Have a look at the following link for possible workarounds...

http://www.mrexcel.com/board2/viewtopic.php?t=89579&highlight=

Hope this helps!

y_not said:
I am using a formula which relies on the user selecting an option form
drop down list. Depending on thier choice the sheet displays informatio
relative to the selection.

This has worked fine for a while but I have been asked to add two extr
items to the drop down list. The effect of this that Excel reports a
error because (I think) there are too many IF statements.

Does anyone have any idea of how I can get around this, please?

If the formula makes any sense I have added it below: -

=IF(Chose="Birkenhead",B70,IF(Chose="Bradford",B123,IF(Chose="Derby",B176,IF(Chose="Leeds",B229,IF(Chose="Rochdale",B282,IF(Chose="Sainsbury's",B335,IF(Chose="Skelmersdale",B388,IF(Chose="Basingstoke",B441,'C:\Document
an
Settings\Desktop\Test\Tracker\[Skelmersdale.xls]Driver'!B8+'C:\Document
an
Settings\Desktop\Test\Tracker\[Sainsbury''s.xls]Driver'!B8+'C:\Document
and Settings\Desktop\Test\Tracker\[Rochdale.xls]Driver'!B8+'C:\Document
and Settings\Desktop\Test\Tracker\[Leeds.xls]Driver'!B8+'C:\Document
an
Settings\Desktop\Test\Tracker\[Bradford.xls]Driver'!B8+'C:\Document
an
Settings\Desktop\Test\Tracker\[Birkenhead.xls]Driver'!B8+'C:\Document
and Settings\Desktop\Test\Tracker\[Derby.xls]Driver'!B8))))))))

The list is of town names, "Chose" is the named cell of the drop dow
and "Region" simply totals the data for the eight town
Click to expand...
 
D

Domenic

If you would rather not have your workbooks opened or use th
workarounds mentioned in that link I gave you, then you could use th
following formula instead of the one I offered for D1, as Dave ha
indicated...

=IF(C1<>"","",[Birkenhead.xls]Driver!B8+[Bradford.xls]Driver!B8+etc...)
Let A1:A8 contain the following...

Birkenhead
Bradford
Derby
Leeds
Rochdale
Sainsbury
Skelmersdale
Basingstoke


C1:

=IF(ISNUMBER(MATCH(Chose,A1:A8,0)),INDEX(B70:B441,MATCH(Chose,A1:A8,0)*53-53+1),"")


D1:

=IF(C1<>"","",SUMPRODUCT(N(INDIRECT("'["&A1:A7&".xls]Driver'!B8"))))

OR

=IF(C1<>"","",SUMPRODUCT(N(INDIRECT("'C:\Documents an
Settings\Desktop\Test\Tracker\["&A1:A7&".xls]Driver'!B8"))))

Note that the range A1:A7 does not include a reference to A8. Also
note that referenced workbooks need to be opened for the formula t
work. Have a look at the following link for possible workarounds...

http://www.mrexcel.com/board2/viewtopic.php?t=89579&highlight=

Hope this helps
Click to expand...
 
Y

y_not

Just a note to say thanks for sorting oout my problems.

I used Dave Peterson's suggestion simply because it seems the easie
option (and it worked), howeverI have printed out a copy of Domenic'
response because I'm sure that that will be really interesting workin
out how the formulas work and thats the way I try to learn new stuff.

So once again guys - thanks for the help and advice.

Cheers

Tony :
 
Top