drag formula?

[lepend]

Hi all,
Ive got the following formula and want to drag it but changing G:G in relation to the column

=SUMIFS(INDIRECT("'"&$F$4&"'!G:G"),INDIRECT("'"&$F$4&"'!C:C"),$E28,INDIRECT("'"&$F$4&"'!A:A"),$E$4)

Sorry, I didnt make this formula and really dont understand so well how this works so any explanations need to be for a complete newbie
Any help appreciated...
Thanks
lepend

 

[Jonmo1]

What's the formula as you have it now?

 

[lepend]

=sumifs(indirect("'"&$f$4&"'!f11:f5000"),indirect("'"&$f$4&"'!c:c"),$e11,indirect("'"&$f$4&"'!a:a"),$e$4)

 

[Jonmo1]

You haven't changed all the ranges

=sumifs(indirect("'"&$f$4&"'!f11:f5000"),indirect("'"&$f$4&"'!c:c"),$e11,indirect("'"&$f$4&"'!a:a"),$e$4)

Should be
=sumifs(indirect("'"&$f$4&"'!f11:f5000"),indirect("'"&$f$4&"'!c11:c5000"),$e11,indirect("'"&$f$4&"'!a11:a5000"),$e$4)

 

[lepend]

Of coarse... sorry..!

 

[lepend]

Ive changed all formulas in the WB from A:A to A11:A5000 thinking that it would speed up any calculations but it is exactly the same if not slower than before.. any reason why this is? and is there anything else I can do? WB is around 20mbs

 

[Jonmo1]

it's really just the nature of INDIRECT. It's a volatile function (that was discussed in your other thread)
The very existence of INDIRECT causes that formula to recalculate every time anything in the sheet changes.
Even changes unrelated to that formula.
Say you have =Indirect(A1&"!B2")
A1 = Sheet1, so it's really doing
=Sheet1!B2
If anything changes (not just A1 or B2, Anything) then the formula recalculates.

This isn't a terrible thing if you have only a few formulas using indirect. But it sounds like you have many, like thousands.
That will have very noticeable impact on performance. And there's not much you can do about it.
INDIRECT is the only way (without a VBA solution) to use a cell reference to identify the sheet name in a formula.

 

[lepend]

Thanks again Jonmo1.. I guess I have my answer then... The only thing I was thinking to lighten the load on the WB was to maybe split it into two WB's and then use some type of hyperlink.. would this be a better solution?

 

[Jonmo1]

How many different pages are there?

I'd recommend combining all the sheets' data into 1 sheet, while adding a column to show the original sheet name.
Then you can add that column as a criteria to your sumifs. No longer needing indirect.

 

[lepend]

basically what ive got is several sheets with data (listed and calculated by days) then the sheets that Ive got indirect formulas are consolidating the information into weeks, months, and specific days, mon, tue, wed etc... thats a lets say snap shot of a year depending what year i put into that sheet... so I can see all information for that year... I really dont know how to do this another way?