Alternate for volatile function Indirect.

[cshetty]

I have a sheet which consolidates (Sums up) the value from identical sheets for every month. Month sheets are named as 01-24, 02-24 and so on. Presently I am using this formula and I am getting the results as wanted, but the processing is too slow.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$S$5:OFFSET($S$5,MONTH($P$1)-1,0)&"'!$B$4:$B$600"),B4,INDIRECT("'"&$S$5:OFFSET($S$5,MONTH($P$1)-1,0)&"'!$J$4:$J$600")))

S5 to S16 I have listed the sheet names for all 12 months as 01-24 , 02-24 .... 12-24
P1 is the month end date in consolidation sheet up to which I want to sum up the values.
B4 is the name of client in consolidation sheet.
B4:B600 is the name of the client in each sheet.
J4:J600 is the value of sales in each monthly sheet.

The reason for using the offset function is to eliminate future months for which the sheet is not yet created. For example at the moment I have sheets from 01-24 to 11-24

Hope I was able to explain properly. Help Please.

 

[jkpieterse]

The solution I would advocate is not to use monthly sheets, but to place your data in a single table. You can then either use a month slicer on the table to create a "monthly" sheet, or you can use a single month report that pulls data from the table using a cell which you use to select the month.

 

[cshetty]

The solution I would advocate is not to use monthly sheets, but to place your data in a single table. You can then either use a month slicer on the table to create a "monthly" sheet, or you can use a single month report that pulls data from the table using a cell which you use to select the month.

Hi
I got your point. But as an example, I mentioned only sales value here. But the month sheet has many other values which need to be consolidated. Having all month data in one sheet, makes it messy I feel.
This is my thought. I still would like to have separate sheet for each month.

 

[RoryA]

Having all month data in one sheet, makes it messy I feel

Quite the opposite, I would say.

In any event, there is no non-volatile equivalent to INDIRECT.

 

[Alex Blakenburg]

You could consider using Power Query to consolidate your monthly sheets into a single sheet and then applying your Sum formula to that.