Automatically Extending a Named Range

[aaron_brown99]

I have a range named "database" that I add to using the built-in data form, and it updates the range when I enter new records.
However, sometimes I need to add records by importing rows from another spreadsheet (hundreds at a time). Is there a way to have the named range extend automatically when I paste rows in like this?

 

[Aladin Akyurek]

On 2002-04-28 15:38, stevesian wrote:
Aladdin or others: can you help me again?

The first instance of this dynamic named range works flawlessly.

I tried to do it in the same workbook but with a different range.

Since I used DATABASE and ENDROW the first time, I created the following two new names, to reference the table in my Remaining Limits sheet:

EndRowLimits:
=MATCH(9.99999999999999E+307,'Remaining Limits'!$A:$A)

DatabaseLimits:
=OFFSET('Remaining Limits'!$A$9,0,0,EndRowLimits-8,10)

The range I want to use begins on A9 and is 10 columns wide. The first column A contains formulas. It is a continguous data table (all cells are full of something).

I copied the formulas for all four name ranges to cells. The original MATCH function correctly evaulates and returns a number (last row in the original table). The new MATCH function does not evaluate and returns a #N/A.

Consequently, the DatabaseLimits name range formula can't, I guess, evaluate off an invalid EndRowLimits name range formula.

Anyways: your suggestions are appreciated.

I tried deleted all above rows and making the data range start at $a$1, didn't help (and not desireable). Tried killing the first column, since it contains formulas, not values, didn't help).

Please help!

Thank you.
This message was edited by stevesian on 2002-04-28 15:49

What is the data type of 'Remaining Limits'!$A:$A?

If it's not numeric, is there any other column in the target range which is numeric and changes along with A?

Aladin

 

[stevesian]

well, currently, I guess the first column of the Remaining Limits list contains concatenated formulas, based on column B.

Column B is a text column as is column C and D. Column E is the first numeric column.

Are you saying that the first column has to be numeric? This is not the case for my other use of the dynamic named range, previously solved by you.

DO you need further information?

Thanks,
Steve.

 

[Aladin Akyurek]

On 2002-05-02 17:05, stevesian wrote:
well, currently, I guess the first column of the Remaining Limits list contains concatenated formulas, based on column B.

Column B is a text column as is column C and D. Column E is the first numeric column.

Are you saying that the first column has to be numeric? This is not the case for my other use of the dynamic named range, previously solved by you.

DO you need further information?

Thanks,
Steve.

Steve,

It's just a matter of computational efficiency. If you can use a column of numeric type, please use that column if it accurately reflects the changes to the data area.

I'd suggest for EndRowLimits instead of:

=MATCH(9.99999999999999E+307,'Remaining Limits'!$A:$A)

one with the E-column if that reflects dynamic changes to your data area:

(1)

=MATCH(9.99999999999999E+307,'Remaining Limits'!$E:$E)

If you would insist on using A-column because there is a good reason for it, then the formula for EndRowLimits will be:

(2)

=MATCH(REPT("z",60),'Remaining Limits'!$A:$A)

But, go for (1) if possible. The latter is meant for purely textual/alphanumeric data areas.

Aladin

 

[stevesian]

Good god, this is beyond my comprehension.
Thanks for your very timely reply, Aladdin. I'll try it out tomorrow.

If you're feeling particularly frisky: do you feel like explaining /howwhy this function works?

Don't worry: I understand if you do not wish to pursue the topic.

I must begin to learn Visual Basic, myself.

Will let you know how it goes tomorrow.

Thanks very kindly.
Steve