Linking to worksheets using a formula

[FloggingDolphin]

Hi there,

I am trying to do a link to a worksheet based on a v-lookup.

It's a bit complex to exlpain so I will use an example.

If I have 5001 worksheets.

1. The first worksheet contains numbers 1 to 5000.
2. The 5000 other worksheets contain data for those numbers.
3. Instead of typing in a worksheet the link: =1!K17 I would like it to look at the worksheet based on another cell. Eg. I have a cell (A21) containing the number 1. So I would like to type something like =(A21)!K17 instead.


Here's another example: I have a main work sheet with the Monday Tuesday Wednesday and Thursday data and four worksheets with those names. To link I would type eg. ='Tuesday'!A4 but instead I want it to use a cell that has the word tuesday in it (Z26) containing the word 'Tuesday'. Ie. I would type something like =(Z26)!A4 instead but it doesn't appear to work.

Hope I managed to explain properly.
thanks in advance!
FD

 

[Brian from Maui]

Have a look at the INDIRECT function. Do you REALLY have 5001 worksheets in a workbook?

 

[FloggingDolphin]

Thanks..I'll check it out.

I only have about 50 worksheets. But I still need to let the formula choose the worksheet based on the precent cell (sort of like a v-lookup).

fd

 

[FloggingDolphin]

Hi...

I still can't seem to get it. I have done a concatenate that comes up with the following in cell AQ30.

=VLOOKUP("TOTAL",'remun 250110'!$J$51:$AK$51,23,FALSE)

I put the indirect function in another cell (=INDIRECT(AQ30)) but it does not work. Comes up with #REF.

I have tried out the above VLOOPUP formula on its own and it works.

Hope someone can help!
FD

 

[FloggingDolphin]

Hi

I got it now, you need a v-lookup formula that looks something like this:

=VLOOKUP("TOTAL",INDIRECT(AL26),23,FALSE)

And AL26 would contain something like:

="'Remun "&E26&"'!"&AN26

With E26 or any cell containing a worksheet name and AN26 containing table array.

Thanks for helping me getting there!

FD