Use crosstab column heading in a calculation

Tech-Archive recommends: Fix windows errors by optimizing your registry

Is it possible to use the column name, which is a pivot of the first column
in the underlying table, in a calculation?

Essentially, I want to create a matrix, based on the values in the first
field of a recordset. There are 145 records in the rs, values 0-144 in the
first field, "SpotMonth". I want to create a matrix of 145 columns and 145
rows. For argument's sake, let's say I want to display the product of the 1st
field and each of the column headers:

Column: 0 1 2 3 4 etc...
SpotMonth: 0 0 0 0 0 0
1 0 1 2 3 4
2 0 2 4 6 8
3 0 3 6 9 12
4 0 4 8 12 16

Any suggestions?

I've managed to create a crosstab query that pivots the values in the
"SpotMonth" field and uses them as column headers, but I don't know how or if
I can reference that column name value in a calculation.

Thanks,

Bill

--
Bill Reed

"If you can't laugh at yoursel, laugh at somebody else"

Message posted via http://www.accessmonster.com
.

Relevant Pages

  • Re: Pivot Table Cycling Through Page Fields Automatically
    ... creating a new pivot table which matched your results. ... I have the curious result of seeing ONLY ONE set of data when the first column is identical in both of the worksheets with data. ... For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. ...
    (microsoft.public.excel.misc)
  • Re: Several Pivot Table Questions
    ... Item and Calculated Field which can be found on the Pivot table Toolbar under ... > the longer any calculation takes. ... But pivottables are different. ... >> (small note, I use Excel XP at work so these are, I believe, specific to ...
    (microsoft.public.excel)
  • Re: Calculated Field on PT data shown as % of Column
    ... Perhaps you could add fields to the source data, and calculate the percent of column there, for each row. ... In the pivot table, sum these fields in the data area, and calculate the differences. ... since the calculation is on two different ... In reality, there are hundreds of agents, and the names of these ...
    (microsoft.public.excel)
  • Re: Pivot table problems. Bad ratio.
    ... When you create a calculated field, the Sum each field is used in the calculation, no matter which function you're using for that field in the pivot table. ... Replies give rise to sales. ...
    (microsoft.public.excel.misc)
  • Re: Custom Calculations
    ... This will show columns for Actual and Forecast, and you could manually hide the one you don't want. ... "Data Source" and in my pivot table I am grouping forecast and actual along the header of the pivot table. ... "Debra Dalgleish" wrote: ... a custom calculation which I was able to to at first but everytime I refresh the pivot it bombs out & closes. ...
    (microsoft.public.excel.misc)