Pivot Table - Can you automatically determine range of sourc

[Mopacs]

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

 

[Mark W.]

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

 

[Mopacs]

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

 

[Mark W.]

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.

 

[Mopacs]

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

 

[Mark W.]

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?

 

[Mopacs]

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

 

[Mark W.]

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

 

[Mopacs]

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

 

[Mark W.]

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.