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

[karraj]

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

[Mark W.]

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 ]

[Mopacs]

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 ]

[Mark W.]

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 ]

[Mopacs]

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

[lenze]

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 ]

[Dave Hawley]

Hi


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

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

[Mark W.]

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.

[Mopacs]

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 ]