Variable/Dynamic Sheet Reference in Formula!?! HELP!

Hi All,

Not sure my title is the best way to describe my problem; so i'll explain...

Basically, what I want to achieve is a way of, rather than have to type out loads of formulas, or copy the formula out to something like word, do a find and replace and then paste back, that I can reference a SHEET name in a formula by using the TEXT contained in a cell reference of my choosing.

Example...

• I have Created a Sheet Called EPL
• I have a formula, for example =EPL!A1

Then what I want is, if I create a new sheet called say...ECH...to have a way of not having to update all my formulas to have ECH! in rather than EPL!...

SO...my thought (not sure if it's the best) was to have a hidden column in the sheet where the formula is, with EPL or ECH in the cells, and then a way that the formula would search a sheet with the name in that cell and then the range as per normal...

To illustrate...

=EPL!A1 might become (in principle, not written correctly as i don't know how!)...=A1!A1...the first A1 being the cell on this sheet which is dictating the name of the sheet to search in.

Now, I realize this will mean me being VERY careful with how I name my sheets and what i type in my hidden column, but that's something i'm okay with...just need a way to do what I'm after!

I hope this makes sense, just in case I have created a sample spreadsheet showing what i mean...

Thanks in advance guys!!



Chris

Hi Teylyn!

Thanks so much for the response; apologies it took me so long to get back been away from the project for a few days with work.

Well, the basis of it is that I have many different teams in my business for which i'm responsible for analyzing for the business. They all have their own tab (hence the sheet name query originally). On their own tabs they're measured on a range of performance measures. This is then collated and compared on the front sheet. The reason indirect (or something similar) was useful was I could just update the team name in one column, and the same formula would then return or calculate based on the same range on a different sheet.

Your indirect idea does seem to work thus far in my own small tests...my question though is how would I apply that indirect formula to this formula!?

Please Login or Register  to view this content.

Thanks Teylyn! Lemme know if you need an example with the above range populated with data for a test your end!

Thanks!


Chris