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