Named Range changes length. Is that an issue??

[StuartM1]

Hi folks,

I'm using a bunch of named ranges. Let’s say that one is 10 rows long on day one. But on Day 2, when I update it, it needs to be 15 rows long.

Will I have to go into each named range and edit the length of eachone manually?

If that’s the case, should I just make the named rangeextremely long with many blank cells, or would that cause issues?

Thanks for any input!!!!

 

[Aladin Akyurek]

Care to post a named range, its current reference, and indicate its contents (dates, time values, dollar values, counts, text. etc.)?

 

[StuartM1]

Thanks Aladin. I tried to attach an image or short lists but i cannot.

These would be just simple lists of 10 cities, 10 yyyy/mm entries, and 10 different numbers.

They would be named differently. But the current length is ten. When i update the data, it will not be exactly 10 rows anymore. It might be higher or lower.

So just wondering what maintenance i need to do to Named Ranges when i dump in new data and the number of rows is longer or shorter. Or shoud i just make the name range very very long and it would have many blanks.

Thanks!!!

 

[Aladin Akyurek]

Please try to indicate the current ranges for cities, for dates, and for numbers. What I' asking for is something like A2:A11 in Sheet1...

 

[StuartM1]

Thanks Aladin.

All on the same sheet. Cities in A2 thru A11, Dates in B2-B11, Numbers in C2-C11. So the named ranges end at row 11.

But the following week I grab new data. And when i paste that new data into the ranges, it will probably be a different number of rows. So I'm wondering what's the best way to update the named ranges to grab the new data.

Thanks again!

 

[Aladin Akyurek]

I'll assume Sheet1 (Adjust to suit.).

1. Define Lrow in Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$B:$B)

2. Define Cities as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

3. Define Dates as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

3. Define Numbers as referring to:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)