K
Keith R
XL2003. I'm working in a workbook that has several pivot tables. I need to
graph some data from the tables, but I'm aware that I can't use straight
worksheet range references because someone might sort the pivot table
fields, especially if new data is added that will end up early in the list
of items after the sort.
In my actual application, I am using one set of pre-made graphs, and just
changing one cell value to change the (named range) references for all the
graphs to a new line. Conceptually, think of it this way:
Left side of pivot table #1 = salespeople, top is months, center is dollars
in sales
(there are 6 pivot tables, each pulling data from various data sources)
The first graph is the equivalent of graphing the sales per month for Joe
Salesguy, then if I change my cell reference it should grab the data for
Jill Saleslady, etc.
So, I need a reliable way to find one particular row of data to graph,
regardless of where it is in the pivot table. I thought I could use
=match(a,b,false) against the pivot table area to find the correct row, then
use a named range to grab the data in that row for graphing.
However, when I try to use the match function, it returns N/A against the
table [=MATCH(C1,A5:A23,FALSE)]. I confirmed that the values are identical
with my match by doing a direct comparison, e.g. =C1=A7 (returns true).
So, what is the preferred method to grab a particular row from a pivot
table, given that the table may re-order? Is there a workaround for the
match function, or am I maybe using it incorrectly?
Thanks,
Keith
graph some data from the tables, but I'm aware that I can't use straight
worksheet range references because someone might sort the pivot table
fields, especially if new data is added that will end up early in the list
of items after the sort.
In my actual application, I am using one set of pre-made graphs, and just
changing one cell value to change the (named range) references for all the
graphs to a new line. Conceptually, think of it this way:
Left side of pivot table #1 = salespeople, top is months, center is dollars
in sales
(there are 6 pivot tables, each pulling data from various data sources)
The first graph is the equivalent of graphing the sales per month for Joe
Salesguy, then if I change my cell reference it should grab the data for
Jill Saleslady, etc.
So, I need a reliable way to find one particular row of data to graph,
regardless of where it is in the pivot table. I thought I could use
=match(a,b,false) against the pivot table area to find the correct row, then
use a named range to grab the data in that row for graphing.
However, when I try to use the match function, it returns N/A against the
table [=MATCH(C1,A5:A23,FALSE)]. I confirmed that the values are identical
with my match by doing a direct comparison, e.g. =C1=A7 (returns true).
So, what is the preferred method to grab a particular row from a pivot
table, given that the table may re-order? Is there a workaround for the
match function, or am I maybe using it incorrectly?
Thanks,
Keith