named ranges and vlookups

[Sarah]

I am trying to create a plan & forcast tool and to do this
I need to look up the same information in different sheets
and return the information to certain cells so
calculations can be applied. I am happy using Vlookups but
am having serious problems with the named ranges and
sheets within the pages as they contain identical formats
and information.

For example - plan03 compared to plan04.
I want to select the plan years to be compared from drop
down menus which has been successful, I have then named
the sheets the same as the subject so "Plan03".
I then want to return information for calls answered for
each month. normally I would name this range "callsA" and
then write a vlookup to reference to that particular area.
This time I want excel to look at the plan year, decide
which sheet to go to and then return the information in
that range. The problem is this area apears across six
different sheets and excel wont allow me to name ranges
within the same workbook with the same name. Stuck..!

 

[Mike]

One idea would be to use a dynamic named range. Using the
INDIRECT function, you can create a range reference that
is defined using a text string. For example if you have
callsA referencing Sheet2!A1:A20, but also need it to
reference Sheet3!A1:A20, or Sheet4!A1:A20, depending on
the plan year, you could use:

callsA=INDIRECT(Sheet1!A1&"!A1:A20")

Now you can enter Sheet2, Sheet3 or Sheet4 in cell A1 of
Sheet1, and callsA will reference the correct sheet. You
could use a formula in Sheet1!A1, or a data validation
list, or what ever you are using to pick the plan year to
ensure the correct sheet name is entered in cell A1.