Dynamic range without COUNTA

[yousafkhan1976]

Hi Everyone, my first post here!

I have searched and tried many different ways to create a dynamic named range where the starting cell column remains the same but the row can move depending on the number of rows inserted above. I thought the following may work, but even though if the formula is used in a cell I get the correct range defined "$A48:$AF83", but when I put the same formula in the "Refers To:" section of the named range, it does not work.

="$A"&((MATCH("Staff Effort, Hours",Resources!A:A,0)+1)&":$"&(SUBSTITUTE(ADDRESS(1,COUNTA(Resources!45:45),4),1,""))&((MATCH("LastRowHours",Resources!A:A,0))-1))

Any help would be greatly appreciated.

 

[My Aswer Is This]

Here is what I do when I want a Dynamic Named range.

Lets assume we want the Dynamic Named Range in Range("A5:A15")

And as we add more data into Ranges("A16") and below The Range expands downward

But we still want the Top of the Named range to stay at A5 even if we add more rows above row 5

Do this:

Select the Range("A5:A15")
Choose Insert Table

Now choose The same Range and Name it "Mom"

Now as you add more data Below A15 this will still be part of the Range named "Mom"

Try this and see how it works.
It always works for me.
Presto: A Dynamic Named Range

 

[yousafkhan1976]

Thanks! that does work and pretty simple. I will just document how to add rows/columns for users of the workbook.