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

 

[Joe4]

Sorry didnt know i was breaking any rules

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[lepend]

Thanks a lot Jonmor1

 

[lepend]

If I change the following "instead of G:G, use G2:G1000" =SUMIFS(INDIRECT("'"&$F$4&"'!F11:F10000"),INDIRECT("'"&$F$4&"'!C:C"),$E11,INDIRECT("'"&$F$4&"'!A:A"),$E$4) I get the #VALUE ! error

 

[Jonmo1]

You have to do it to ALL the ranges, not just G:G

 

[lepend]

You have to do it to ALL the ranges, not just G:G

ok.. I tried doing this with find and select option but it wouldnt work.. how can i do this without having to do it manually.. it will take me until this time next year..

 

[Jonmo1]

Really?

You changed F:F to F1:F10000 pretty quick, surely less than an hour...
Seems you could change A:A to A1:A10000 and C:C to C1:C10000 in pretty much the same amount of time.

Maybe I'm missing something, I'm only referring to the single formula you've posted.

 

[lepend]

Ah, so if i use find and select/replace on each column then when ive completed all columns the VALUE error will disappear..?

 

[Jonmo1]

All else being equal, and assuming you did not have any #Value! errors to begin with before you even posted here...
Then yes, if each range in every sumifs formula is of equal dimensions, then they will not result in #Value!.

=SUMIFS(A1:A100,B1:B105,"criteria") - This will return #Value! because of the unequal dimensions of the ranges.
=SUMIFS(A1:A100,B1:B100,"criteria") - This will not (at least not due to the range dimensions).

 

[lepend]

Thanks a lot again...

 

[lepend]

Ive changed all the cell formulas and I am getting the VALUE error in all cells.. Ive double checked that all columns that ive changed are without differences and all is ok there.. what else can it be?