Dynamic Range in a Formula? Something like End xlDown

[NicholasL]

So I have a function in a spreadsheet that needs to target a dynamic range.
It's a custom function but if you imagine it as the SUM function it will work the same.

So lets say I have a cell that is "=SUM(CD97:CD288)"
In the future I'll add another row of data but I want to avoid having to go in manually and changing it to "=SUM(CD97:CD289)"

What I'm about to propose isn't the correct syntax (I tried it as a shot in the dark) but I think it illustrates what I want well enough:

"=SUM(CD98:CD97.End(xlDown))" -- I know that's VBA and doesn't belong in a formula (right?) but how can I do this?

If you have any questions let me know, thanks for the help!

[NeedForExcel]

Select the Table from where the data is derived. Press Ctrl + t.

This will activate the Tables feature. Re-type the formula again.. And your ranges will be dynamic.

Else you can also use the offset function.

Cheers!

Deep

[NicholasL]

Okay, good news bad news.

Good news: it sort of works. If I go to the end of the range defined as a table and write in a new value the table automatically expands and the value I have set for the formula expands to include the new entry into the table.

Bad news: when I copy/paste the formula that makes up the cells of the table down another row the table doesn't automatically expand to include it? Any idea why this might be happening? I need it to expand the table automatically when I copy/paste the formula down a row.

[NeedForExcel]

Well the Table feature has a dynamic range which is absolute.. Even if you drag down, it shud call the same range in the formula..

[codeslizer]

Why not create a dynamic named range?

Go to Formulas tab > Name Manager > New... Type the name under Name : - say "RngToSum" and in the Refers To field type the following :

Please Login or Register  to view this content.

Replace 10000 with whatever bottom limit you want. OFFSET() function would re-size based on the counts available between the range. Only thing is you'll have to make sure that the rows should be continuously filled for this, otherwise, it might result in some un-expected results.

And after creating the dynamic named range, you can change your formula to

Please Login or Register  to view this content.

[NicholasL]

instead of a having a bottom limit that just extends down as far as I want, could I tell it to only go as far as consecutive non-empty cells go? Similar to the End(xlDown) code works?

[NicholasL]

Okay, so I just figured out a solution.
In case anyone else has a need for this.
so I need to have this spreadsheet set up so it can be updated in the future, I'm doing that mostly by macro (and now more so).
To calculate these formulas on a range, I select the range in VBA using .End(xlDown) and then name that range.


This is what the VBA I just tested looks like

Please Login or Register  to view this content.

then I just have the formulas I need, just reference whatever the name is that I give it.

For instance, I'd have one cell be "=SUM(overall1)"
and then when I run the macro it will update.

[wise_wallflower]

Hi,


How do I make this work for SUMIF's formula where I have it linked to other sheets? I have the same requirement to make the formula dynamic so that when I add more rows at the bottom, the formula updates automatically?

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread