drag forumula?

[loopiloo]

Hi all,
I have the following formula and want to drag it.. but I need the G:G to change in relation to the column

Please Login or Register  to view this content.

Can anyone help
Thanks
loopiloo

https://www.mrexcel.com/forum/excel-...ml#post4992050

[Glenn Kennedy]

try this:
=SUMIFS(INDIRECT("'"&$F$4&"'!"&CELL("address",G1)&":"&CELL("address",G100)),INDIRECT("'"&$F$4&"'!C:C"),$E14,INDIRECT("'"&$F$4&"'!B:B"),$E$4)

but don't use whole column references with volatile functions... unless you're very patient. Adjust all ranges to be the same.

[loopiloo]

Thanks Glenn, but I need a bit more help in getting this to work... I really dont understand what you mean "dont use column references with volatile functions" it is rather slow though
I didnt make this formula.. and Im really not that good with formulas unfortunately...

[Glenn Kennedy]

INDIRECT is a volatile function. It recalculates EVERY time you change something on the sheet. If you use whole column references (G:G), Excel will calculate the formula for 1,000,000-plus rows, for every cell this formula is in, every time you change something. So, sit back and watch your hair turn grey as you wait.

=SUMIFS(INDIRECT("'"&$F$4&"'!"&CELL("address",G2)&":"&CELL("address",G100)),INDIRECT("'"&$F$4&"'!C2:C100"),$E14,INDIRECT("'"&$F$4&"'!B2:B100"),$E$4)

the formula above will still recalculate everytime you change something, but it will ONLY go down from row 2 to row 100, not by more than a million rows.

Where are you? mainland Europe, or elsewhere (please amend your profile to show location and Excel version: both are important for provision of efficient solutions)

[loopiloo]

Im in the uk and its excel version 2007.. updated my profile like you said..
The G:G is in a different sheet and would need to calculate up to 5000 rows.. I change the sheet name in cell F4 which gives me the information i need.. hope im making sense..

[Glenn Kennedy]

The location of G:G is picked up from F$4. Further changes to the CELL("address",G2)&":"&CELL("address",G100)) bit are not needed (except, of course, the ranges... given that 2 to 100 is incorrect).

[Glenn Kennedy]

I had a feeling you might be UK based.... I'm old enough to remember Loopy Loo - which may have been the inspiration for your username...

[loopiloo]

It that long ago I cant remember the inspiration for my username...

[alansidman]

crossposted: https://www.mrexcel.com/forum/excel-...g-formula.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[loopiloo]

Sorry didnt know i was breaking rules..

[Glenn Kennedy]

Is that you sorted now? if not... what issues remain. If so, then you're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[loopiloo]

Will do thanks a lot.. got solution