Automatically Extending a Named Range
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Automatically Extending a Named Range

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Global Crossing
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.?

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Global Crossing
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.



  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    Global Crossing
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. Dont 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. Im posting this as well. if you can, you are a god

    Sincerely,
    Steve Sian
    Vancouver, BC

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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. Dont 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. Im 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

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com