Non-volatile function to consolidate data from various worksheets

[excelstarter1]

Hi guys,

all the time I was using INDIRECT and VLOOKUP to consolidate data from various worksheets within one table. But the process is so time consuming (I dont want to switch to manual calculation). I recently read about "volatile" functions and apparently when using a function like INDIRECT or VLOOKUP, Excel is calculating each cell every time I perform a task or change a cell. Can you suggest a faster (non-volatile) approach? Especially I want to subsitute INDIRECT. For VLOOKUP I could use INDEX + MATCH, right? I read somewhere that INDEX+MATCH is much faster anyways, compared to VLOOKUP...

My data: I have one sheet where I want to collect monthly data , e.g. sales (Jan 2017, Feb 2017, etc.) for various products. The data I want to collect using the formula is stored in separate worksheets within the workbook and are named "Jan 2017", "Feb 2017" and so on. Now I tell my function, get the worksheet name (with INDIRECT and the value in cell A2, A3, etc.) and look up the product in column 1 (VLOOKUP) and give me the values in column 2 (i.e. sales this month for the particular product).

The final result should look something like this:

Consolidated sheet	Jan 2017	Feb 2017	...
Product 1	1234	4234	...
Product 2	312312	123124	...
Product 3	14123	1231	...
...	...	...	...

<tbody>
</tbody>

Thanks!

Regards

 

[Jonmo1]

It's the INDIRECT that is volatile, not vlookup.

And unfortunately, there is no other (non volatile) way to write a formula that references a sheet by the contents of a cell. Indirect is the only way to do that


To avoid indirect, you'd have to manually enter the sheet names into the formulas.
You'd only have to do the top cell of each column, then fill them down.

 

[Jonmo1]

Now Vlookup can cause slow calculations (for reasons other than volatility).
But it depends on how your formula is written, can you post the actual formula ?

Index/Match can be used instead to improve performance.
But the more likely cause of poor performance is using Entire Column References in the formula, like A:B.
Finite ranges are 'far more' efficient, like A$1:B$100

 

[excelstarter1]

Awesome, thank you for the quick reply!!

I will switch to Index/Match and will restrict the range. So far I have been using references like A:A

Is there really no other way to substitute a INDIRECT function?

I use a Swiss (German) Excel version. Not sure if the German formula would help here. And I am not sure about the right translation...

 

[Jonmo1]

If it's really only 12 sheets (months right?)

Then you can do something like this

=VLOOKUP(lookupvalue,CHOOSE(MONTH(B1),'Jan 2017'!A:B, 'Feb 2017'!A:B, 'Mar 2017'!A:B,etc..),2,FALSE)

Assuming that B1 is a date representing the month you want to use.

 

[excelstarter1]

It varies all the time, thats why INDIRECT is a nice approach. Somtimes I have 24 months, somtimes the worksheets are only named 1, 2, 3 ...