Look at certain tabs based on cell text

[shiftyspina]

Hi All

I am trying to build a spreadsheet, but what i want is it to have a summary page and depending of the text in a certain cell look at that tab.

For instance say i have customer A, customer B, and customer C, and in products my formula is

Please Login or Register  to view this content.

If in my summary page in cell A2 through to cell L2 (jan - dec) i have a data valaidation list, of Actual, Budget and Reforecast, and i want the formula to look at these cells and replace "budget" in my above formula with whatver is in cell A2:L2 how can i do this?

I will be using 9 extra tabs customer a budget, customer a actuals and customer a reforecast, and again for customer b and c.

But rather than have to adjust formulas or sheets i would just like to allow the user to choose from the data validation to get the figures they require in the summary tab.

Any help would be great please

Thanks

Dan

[Fotis1991]

Perhaps INDIRECT function can helps you..

http://spreadsheetpage.com/index.php...et_indirectly/

[shiftyspina]

Hi Thanks for that,

I understand a little bit abotu indirects, but i need the formula to look a bit like this.....

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

This formula will have to look at 3 tabs to get the answer, and i can only get the indirect to look at one tab, and also have to look at two criterias using the indirect,

1. being the budget, reforecast or actual
2. being customer a, customer b, customer c

[shiftyspina]

Hi

Im not getting any closer to solving this issue, i have tried indirect, index, match, but just keep getting #ref back, i have built sheets before that look at the cell text and then pick up the tab corresponding to that cell, but cant figure out how to adjust the formula if the cell changes, as i have used text in the forumal rather than cell references, also i have never had to look at more than one tab before, usually just using index and match to look at one tab

I have attached an example if anyone would be kind enought to give me a headstart on this as i have spent nearly 3 hours trying to get this to work, and have got no better than #ref so i know something is wrong in the formulas.

Thanks

Dan

Example.xlsx

[shiftyspina]

Solved!!!!!

Please Login or Register  to view this content.