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-01 09:43, aaron_brown99 wrote:
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?

What is the current exact range Database refers to? Would you also enumerate the type of data that you have in that range such as dates, numbers, etc.?

[lenze]

Don't know if this will work or not, but try inserting rows in your table above the last record and copying the new rows inside the database. BUT a better way is to use a Dynamic Named Range. If you search the board, you should find several references of how to do this

[aaron_brown99]

The current "Database" range is: $A$1:$E$316.

I'm storing inbound call center call volume and answer speed information. Each record includes a date, two number fields, a custom time field ([h]:mm:ss) and a text field for identifying the inbound skillset.

[Aladin Akyurek]

On 2002-04-01 09:52, aaron_brown99 wrote:
The current "Database" range is: $A$1:$E$316.

I'm storing inbound call center call volume and answer speed information. Each record includes a date, two number fields, a custom time field ([h]:mm:ss) and a text field for identifying the inbound skillset.

Assuming that column A houses the dates,

[1] activate the option Insert|Name|Define,
[2] enter EndRow as name in the Names in the Workbook box,
[3] enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,x!$A:$A)

[ Note. Replace 'x' by the sheetname where the database is. ]

[4] Activate Add,
[5] Enter Database as name in the Names in Workbook box,
[6] Enter as formula in the Refers to box:

=OFFSET(x!$A$1,0,0,EndRow,5)

[ Note. Replace 'x' by the sheetname where the database is. ]

[7] Activate OK.

Now, you're ready to use the dynamic range name, Database in your workbook. It will expand or shrink automatically along with deletions from or the additions to the data area.

[aaron_brown99]

Beautiful, it works great!

Unless I'm mistaken, I noticed in the OFFSET formula you reference "5" as the width. IF I later add a column (not likely) to the database, will I need to update the width as well?

Thanks again, this now allows me to COMPLETELY automate my inputs with VB and will save me hours each week!

[Aladin Akyurek]

On 2002-04-01 10:16, aaron_brown99 wrote:
Beautiful, it works great!

Unless I'm mistaken, I noticed in the OFFSET formula you reference "5" as the width. IF I later add a column (not likely) to the database, will I need to update the width as well?

Thanks again, this now allows me to COMPLETELY automate my inputs with VB and will save me hours each week!

Yes, 5 refers to the width.

Aladin

[stevesian]

Aladin this is brilliant.

I tried this myself, per your instructions and increasing the ‘5’ in the original example to ‘31’ (my number of columns).

The resulting database named range is the correct number of columns wide, but it is 4 rows too long.

This creates another bug for me, which is why I want my named range to be exact:

My data range is an external data text file, so it will change all the time.
The current range is $A$5:$AE$1291.

Your DATABASE range calculates out to $A$5:$AE$1295 for some reason. Can you help?

The whole reason for setting the EXACT data range is for the pivot tables I have created from this data range with DATE fields in them: I cannot group the date fields at all, unless the data range is EXACT with no blanks. Don’t ask me why. I found this out by trial-and-error, certainly not through Microsoft resources.

Can you help? Everything in my spreadsheet is automated except this. I’m posting this as well. if you can, you are a god…

Sincerely,
Steve Sian
Vancouver, BC

[Aladin Akyurek]

On 2002-04-09 23:55, stevesian wrote:
Aladin this is brilliant.

I tried this myself, per your instructions and increasing the ‘5’ in the original example to ‘31’ (my number of columns).

The resulting database named range is the correct number of columns wide, but it is 4 rows too long.

This creates another bug for me, which is why I want my named range to be exact:

My data range is an external data text file, so it will change all the time.
The current range is $A$5:$AE$1291.

Your DATABASE range calculates out to $A$5:$AE$1295 for some reason. Can you help?

The whole reason for setting the EXACT data range is for the pivot tables I have created from this data range with DATE fields in them: I cannot group the date fields at all, unless the data range is EXACT with no blanks. Don’t ask me why. I found this out by trial-and-error, certainly not through Microsoft resources.

Can you help? Everything in my spreadsheet is automated except this. I’m posting this as well. if you can, you are a god…

Sincerely,
Steve Sian
Vancouver, BC

Steve,

Use in your OFFSET formula

EndRow-4

instead of just

EndRow.

The reason for this is that your data start at row 5 (to be seen from $A$5).

Aladin

[stevesian]

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 ]