Pivot Table - Can you automatically determine range of sourc
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Pivot Table - Can you automatically determine range of sourc

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

    Default

     
    Hello there,

    I have a question regarding the creation of a pivot table. Is there a way to have excel (macro preferably) automatically determine the range of the source data (for a pivot table) without you having to specify a set coordinates (ie, A2:G2032). In this case the source data will fluctuate in the number of rows every week when I create this pivot table. So one week it may have 2000 rows, the next 1934 rows of data. Any suggestions would be greatly appreciated.

    Thanks,

    Rob

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can have a cell with the value =counta(a:a) to get the last row of the table.
    K

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The best approach is to create a named range (e.g., Database) and create your PivotTable using that range. You can either update there reference to this range as new data is added or make it dynamic.

    [ This Message was edited by: Mark W. on 2002-03-11 11:51 ]

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

    Default

    Good suggestions.

    Ok, lets say I specify the source data as a named range. What would be an example of the VBA code, which would dynamically determine the extent of the range each time?

    Thanks,

    Rob

    [ This Message was edited by: Mopacs on 2002-03-11 11:59 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 11:57, Mopacs wrote:
    Good suggestions.

    Ok, lets say I specify the source data as a named range. What would be an example of the VBA code, which would dynamically determine the extent of the range each time?

    Thanks,

    Rob

    [ This Message was edited by: Mopacs on 2002-03-11 11:59 ]
    No VBA code is required. You either update the data list using Excel's built-in data form (i.e., the Data | Form... menu command) or you define a name that refers to a formula that examines the key field(s) of your data list to determine the extent of the data list.

    [ This Message was edited by: Mark W. on 2002-03-11 14:44 ]

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

    Default

    On 2002-03-11 14:13, Mark W. wrote:


    No VBA code is required. You either update the data list using Excel's built-in data form (i.e., the Data | Form... menu command) or you define a name that refers to a formula that examines the key field(s) of your data list to determine the extent of the data list.

    [ This Message was edited by: Mark W. on 2002-03-11 14:44 ]
    Well you'll have to excuse my lack of knowledge here, but do you have an example of a formula that would examine the key fields of a range? I'm not sure I completely understand how to do that. So you create a named range?

    Thanks,

    Rob

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

    Here is a sample of how to assign a dynamic named range.

    Dynamic Range Formula
    =Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$1:$A$65536)-1,COUNTA(Sheet1!$1:$1)-1)
    Note: $A$1= Anchor Cell Address (Change as needed)
    Sheet1!$1:$1 Anchor Row Number (Change as needed)
    The above formula gives a dynamic range that expands both by rows and columns, using the entire column or row. To limit the number of rows, change 65536 to a lower number (ex. 1000 will limit the range to 999 rows) To limit the number of columns, change the Sheet1!$1:$1 to Sheet1!$A$1:$Col :$1, where Col is the column letter of the last column you want in the range. To use, Choose Insert Name Define. Type a name for your range in the name box and then enter the above formula (with any adjustments) in the refers to field. Change Sheet1! in the formula as necessary






    [ This Message was edited by: lenze on 2002-03-12 09:04 ]

    [ This Message was edited by: lenze on 2002-03-12 09:05 ]

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi


    I have many examples of Dynamic named ranges here that are ideal for Pivot Tables.

    http://www.ozgrid.com/Excel/DynamicRanges.htm

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-12 08:59, Mopacs wrote:
    Well you'll have to excuse my lack of knowledge here, but do you have an example of a formula that would examine the key fields of a range? I'm not sure I completely understand how to do that. So you create a named range?

    Thanks,

    Rob
    Can you provide a sample (10-15 rows) of your data including headers? An easy way to do this is to select an unused cell, type =, select a range of records, press F9, and paste the contents of the formula bar into a reply posting.

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

    Default

      
    On 2002-03-12 09:27, Mark W. wrote:

    Can you provide a sample (10-15 rows) of your data including headers? An easy way to do this is to select an unused cell, type =, select a range of records, press F9, and paste the contents of the formula bar into a reply posting.
    Ok, it looks lenze's example above seems to have worked. I understand the logic, and thats basically what I was looking for.. but I'm open to any additional suggestions of course....

    Here is a sample cropping of this data/worksheet..not all columns are represented either. But lets just say the first field heading below is in column A..and the Row headers lie in Row 3. I dont know if this is what you were looking for, but here ya go:

    {"Age","dob","Age_Cat","Age_Grp","TP","BP";49,19244,"Adult"," 45-64","23","13";82,7314,"Aged"," 80-84","13","13";66,13010,"Aged"," 65-69","13","13";75,9707,"Aged"," 75-79","13","13";61,14802,"Adult"," 45-64","13","13";82,7094,"Aged"," 80-84","13","13";72,11002,"Aged"," 70-74","23","13";67,12698,"Aged"," 65-69","14","13"}

    Thank you (all of you) for your assistance here.

    Rob




    [ This Message was edited by: Mopacs on 2002-03-12 09:46 ]

    [ This Message was edited by: Mopacs on 2002-03-12 09:47 ]

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