Pivot Table - Can you automatically determine range of sourc - Page 2
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

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

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

     
    Assuming "Age" is in column A and is always assigned a value when a record is added, you can define a named range, Database, for a data list occupying columns A:G using the formula...

    =OFFSET($A$1,,,MATCH(9.99999999999999E+307,$A:$A),7)

    This formula will examine fewer worksheet rows in the course of determining your data list's extent.

    [ This Message was edited by: Mark W. on 2002-03-12 09:58 ]

  2. #12
    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:55, Mark W. wrote:
    Assuming "Age" is in column A and is always assigned a value when a record is added, you can define a named range, Database, for a data list occupying columns A:G using the formula...

    =OFFSET($A$1,,,MATCH(9.99999999999999E+307,$A:$A),7)

    This formula will examine fewer worksheet rows in the course of determining your data list's extent.

    [ This Message was edited by: Mark W. on 2002-03-12 09:58 ]
    Thanks for the info...

    I'm still having a bit of trouble with your formula above. I'm guessing I did not enter the correct syntax. I added the worksheet name before the cell reference ('Sheet01'!$A$1)and ('Sheet01'!$A:$A) but the Pivot Table wizard rejected the named region which was given that formula (in this case "sheet01" was the name of the region). Any suggestions or are there additional substitutions needed in your formula above?

    Thanks again,

    Rob

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

    Use the Insert | Name | Define... menu command to create a named range, Database, that refers to...

    =OFFSET($A$1,,,MATCH(9.99999999999999E+307,$A:$A),7)

    ...then at Step 2 of the PivotTable wizard use Database as the reference.

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

    Default

    On 2002-03-12 13:22, Mark W. wrote:
    Use the Insert | Name | Define... menu command to create a named range, Database, that refers to...

    =OFFSET($A$1,,,MATCH(9.99999999999999E+307,$A:$A),7)

    ...then at Step 2 of the PivotTable wizard use Database as the reference.
    Hmm... I did exactly as you said above but when I entered the name of the 'region' in Step 2 above, Excel choked with an error message "Reference Not Valid". Let me make sure I understand correctly... the "7" at the end of the formula refers to the number of columns in the data area? Any other possible problems, syntax-wise?

    Thanks for your patience!

    Rob

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

    Yes, 7 is the number of columns. Why didn't you use Database as the name of the range, and enter Database at Step 2 of the PivotTable wizard?

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

    Default

    Interesting..still rejecting things. Well I dont mean to take up all your time when there are more pressing questions out there!

    Although, I did use "Database" as the range name, as you said...still no luck. Right now I have switched to a much larger range of data... it is 23 columns wide and the header row is actually on row 3 here. Also the pivot table resides on a seperate worksheet w/in the spreadsheet (from the source data) No luck here either! (just one of those days for me!) Anyways, How would the the Offset function change given this setup?

    Again, I truly appreciate your assistance here.

    Rob

  7. #17
    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 13:54, Mopacs wrote:
    Interesting..still rejecting things. Well I dont mean to take up all your time when there are more pressing questions out there!

    Although, I did use "Database" as the range name, as you said...still no luck. Right now I have switched to a much larger range of data... it is 23 columns wide and the header row is actually on row 3 here. Also the pivot table resides on a seperate worksheet w/in the spreadsheet (from the source data) No luck here either! (just one of those days for me!) Anyways, How would the the Offset function change given this setup?

    Again, I truly appreciate your assistance here.

    Rob
    If you A:A is your first column you should be using...

    =OFFSET($A$3,,,MATCH(9.99999999999999E+307,$A:$A)-2,23)

    Additionally, A1:A2 should be blank and every column should have a field label in row 3. I'd recommend that you keep your data list in a worksheet of its own and begin on row 1.

    [ This Message was edited by: Mark W. on 2002-03-12 15:18 ]

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

    Default

    Ok great that seemed to work! Thanks.

    Though I also used the following function, which worked equally well:

    =OFFSET($A$3,0,0,COUNTA($A:$A),23)

    What would be the difference here? any particular advantages or disatvantages (vs the "match" function you embedded).

    Thanks again,

    Rob


  9. #19
    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 14:13, Mopacs wrote:
    Ok great that seemed to work! Thanks.

    Though I also used the following function, which worked equally well:

    =OFFSET($A$3,0,0,COUNTA($A:$A),23)

    What would be the difference here? any particular advantages or disatvantages (vs the "match" function you embedded).

    Thanks again,

    Rob

    Well, COUNTA will also count your field header (in row 3). Also, COUNTA is is "fooled" by empty cells. For numeric data (like 'AGE') =MATCH(9.99999999999999E+307,$A:$A)-2 is much more reliable.

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