Alternative to Vlookup & Indirect

[ashmott]

Hi all

I have a workbook where I am using a combination of Vlookup and Indirect to return values from worksheets in the same workbook.
The reason I am using Indirect is because I am looking up the sheet name by reference to a cell in the active sheet.

The issue is that I have too many cells using Indirect so the speed is unusable.

Can anyone suggest a non VBA alternative to Vlookup and Indirect? I have attached a sample sheet so you can see what I am doing.

Thanks in advance for any suggestions

Ashley

[ashmott]

Alternatively can I change the calculation settings?

[kvsrinivasamurthy]

Pl see attached file with formula.If not Ok with formula we will try Macro.

[ashmott]

Thanks for your response but your formula still uses the INDIRECT function so will get too slow with the volume of data I will be processing.
Is there a method to use not involving INDIRECT?

[kvsrinivasamurthy]

If Indirect is to avoided fully, the formula

Please Login or Register  to view this content.

is to be changed as

Please Login or Register  to view this content.

But every cell name of sheet is to be changed.
I feel Formula is a better solution than Macro.

[ashmott]

Thanks kvsrinivasamurthy
Excuse my poor skills but I cant get it to work. Is it possible for you to put the formula into the attached excel sheet?
Many thanks

[kvsrinivasamurthy]

It is an array formula.
Paste the formula in the cell
Press F2
Press Ctrl+Shift+Enter keys together
You will see{} around the formla like {formula}
Now Ok.
Pl try.