Pivot Tables

gbarclay2001 · Mar 18th, 2002, 04:08 AM

Hi all,

I have a macro which pulls data from the totals row of a pivot table. The amount of rows in the pivot table is determined by records in an ODBC link from a database. The problem is, this means the amount of rows changes and therefore the totals row of the pivot table is not in a static position.

e.g.
the ODBC link has figures relating to four different people - Bob, Harry, Fred and Bert. The sum of their totals is stored in cell F3. So the macro grabs the data from F3. However the following day Jane is added to the data in the ODBC link, pushing the total to cell G3. The macro is still set at F3 so it now picks up the wrong figure.

Make sense? Is there a way around this?

Thanks for any help

Dave Hawley · Mar 18th, 2002, 04:09 AM

Hi

There sure is:

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

These are ideal for Pivot Tables

gbarclay2001 · Mar 18th, 2002, 04:26 AM

Cheers Dave.

Spent ages trying to sort that one out.

Greg

Mark W. · Mar 18th, 2002, 10:13 AM

Use the GETPIVOTDATA worksheet function. See the Help index topic for this function.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 04:08 AM	#1
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	Hi all, I have a macro which pulls data from the totals row of a pivot table. The amount of rows in the pivot table is determined by records in an ODBC link from a database. The problem is, this means the amount of rows changes and therefore the totals row of the pivot table is not in a static position. e.g. the ODBC link has figures relating to four different people - Bob, Harry, Fred and Bert. The sum of their totals is stored in cell F3. So the macro grabs the data from F3. However the following day Jane is added to the data in the ODBC link, pushing the total to cell G3. The macro is still set at F3 so it now picks up the wrong figure. Make sense? Is there a way around this? Thanks for any help

Mar 18th, 2002, 04:09 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi There sure is: http://www.ozgrid.com/Excel/DynamicRanges.htm These are ideal for Pivot Tables

Mar 18th, 2002, 04:26 AM	#3
gbarclay2001 New Member Join Date: Mar 2002 Location: London Posts: 46	Cheers Dave. Spent ages trying to sort that one out. Greg

Mar 18th, 2002, 10:13 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Use the GETPIVOTDATA worksheet function. See the Help index topic for this function.