Big Nested IF functions

[Yogi Bear]

Hi there,

Just when I was about to give up, I found this forum. After reading a number of posts I feel that someone may be able to assist me.

I am writing a forecasting and planning tool for the hospitality industry.

My worksheet makes reference to at least 18 external spreadsheets and I need to be able to pull up an external cell reference based on on a user defined series (using drop down lists / forms).

So currently there are 18 variations on the source of the data, and the only way I can think of to look up the corresponding reference is through an 18 level nested function. I say this because I am not familiar with any methods for using LOOKUP functions to refer to external workbooks.

I have tried a variety of semi-creative solutions, but all rely on heavy nesting.

Can anyone please assist me by giving advice on how to create LOOKUP tables that refer to an external workbook/sheet as part of a string followed by a particular cell reference.

I can provide more details and code if necessary.

Thanking you in advance,

Yogi Bear - newbie

[SteveO59L]

Vlookup takes a form something like this



vlookup("the index field","in worksheet range","return value from column n in that range")

[Joe4]

Take a look at the VLOOKUP function in Excel's help. It gives details, examples, and tips on how to set it up.

[Yogi Bear]

Thanks for your reply steve,

I'm still confused. Following is the code if it assists. Note the external references.

=+IF($G$1="av23",'[Avillion.xls]2002-2003'!C12,IF($G$1="av34",'[Avillion.xls]2003-2004'!C12,IF($G$1="av45",'[Avillion.xls]2004-2005'!C12,IF($G$1="cc23",'[Carlton Crest(1).xls]2002-2003'!C12,IF($G$1="cc34",'[Carlton Crest(1).xls]2003-2004'!C12,IF($G$1="cc45",'[Carlton Crest(1).xls]2004-2005'!C12,IF($G$1="mz23",'[Menzies.xls]2002-2003'!C12,IF($G$1="mz34",'[Menzies.xls]2003-2004'!C12,IF($G$1="mz45",'[Menzies.xls]2004-2005'!C12,IF($G$1="po23",'[PierOne.xls]2002-2003'!C12,IF$G$1="po34",'[PierOne.xls]2003-2004'!C12,IF$G$1="po45",'[PierOne.xls]2004-2005'!c12,IF($G$1="sl23",'[ShangriLa financial data.xls]2002-2003'!C12,IF($G$1="sl34",'[ShangriLa financial data.xls]2003-2004'!C12,IF($G$1="sl45",'[ShangriLa financial data.xls]2004-2005'!C12,IF($G$1="sw23",'[Swissotel Sydney.xls]2002-2003'!C12,IF($G$1="sw34",'[Swissotel Sydney.xls]2003-2004'!C12,IF($G$1="sw45",'[Swissotel Sydney.xls]2004-2005'!C12,))))))))))))))))))))))

Any solutions gratefully accepted.

[SteveO59L]

I think that I would make a table listing the index and source.

av23.............'[Avillion.xls]2002-2003'!C12
av34.............'[Avillion.xls]2003-2004'!C12


etc

It would be a lot easier to maintain.

[Yogi Bear]

Cheers Steve.

The problem is that c12 is only one cell in a table of N times 54 cells. So as you can understand, setting up a table like that would be rather ineffecient.

I don't know what to do!!!

[Yogi Bear]

it is a bit of a sticky question

[hatman]

I think that Steve's solution has merit, it just needs a little tweak to get you there. In the table, rather than typing ='[Avillion.xls]2002-2003'!C12 (which gets you the value in that cell), use this instead: ''[Avillion.xls]2002-2003'!C12 (that is NOT a double quote, it is 2 single quotes, it gives you the text representing the address of the cell).

When you use the Vlookup function (let's assume in cell a3) you get a text string that represents the address of the upper left cell of the table from the correct file and sheet. Now you type the formula =OFFSET(INDIRECT($A$3),ROW(A1)-1,COLUMN(A1)-1) and copy it N times 54 to correspond with the size of the table on each referenced sheet. Now when the user selects a new set of criteria, the entire table changes to correspond with the contents of the proper table... I think that gets you there... I tested it on some local files of my own and it seems to work, if I understand your goal correctly.

EDIT: I fixed the formula above: as written it misses the first row and column, the -1 on row and column is important.

[Yogi Bear]

Hatman,

I am following up on your solution.

Will let you know how it goes.

Yogi

[Yogi Bear]

Hatman,

It took me a few minutes to decipher your solution. However it works a treat and solves my problem to a tee.

Thank you very much.

Yogi