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",B12 3,IF(Chose="Derby",B176,IF(Chose="Leeds",B229,IF(C hose="Rochdale",B282,IF(Chose="Sainsbury's",B335,I F(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


--
y_not
------------------------------------------------------------------------
y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=394172

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 wrote:

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",B12 3,IF(Chose="Derby",B176,IF(Chose="Leeds",B229,IF(C hose="Rochdale",B282,IF(Chose="Sainsbury's",B335,I F(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

--
y_not
------------------------------------------------------------------------
y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=394172

--

Dave Peterson

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 and
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 to
work. Have a look at the following link for possible workarounds...

http://www.mrexcel.com/board2/viewto...579&highlight=

Hope this helps!

y_not Wrote:
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",B12 3,IF(Chose="Derby",B176,IF(Chose="Leeds",B229,IF(C hose="Rochdale",B282,IF(Chose="Sainsbury's",B335,I F(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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=394172

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

=IF(C1<"","",[Birkenhead.xls]Driver!B8+[Bradford.xls]Driver!B8+etc...)

Domenic Wrote:
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 and
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 to
work. Have a look at the following link for possible workarounds...

http://www.mrexcel.com/board2/viewto...579&highlight=

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=394172

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

I used Dave Peterson's suggestion simply because it seems the easier
option (and it worked), howeverI have printed out a copy of Domenic's
response because I'm sure that that will be really interesting working
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 :)


--
y_not
------------------------------------------------------------------------
y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=394172