Automatically Extending a Named Range

aaron_brown99

New Member
Joined
Mar 31, 2002
Messages
20
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top