Fill all the cells of a pivot table

[Fambrius]

Hello.

I have two pivot tables (each one in a different worksheet) and, next to
them, I have created several columns to make some calculations (without
using the calculated value function because it doesn't solve what I need),
ColumnD in my example.

Imagine that in each pivot table I has these columns:

Column A Column B ColumnC ColumnD ColumnE
Person1 House1 Item1 Calculation
Person1.House1
Person1 House1 Item2 Calculation
Person1.House1
Person1 House2 Item3 Calculation
Person1.House2
Person1 House2 Item4 Calculation
Person1.House2
Person2 House3 Item1 Calculation
Person2.House3

The problem is that I have to make more calculation using the results of the
calculations I made next to each pivot table.

The solution I've thought is to put, next to each calculation that I need to
use further, a reference to it composed by two columns of the pivot table,
ColumnE, so I can use it as an unique reference to look for it when I want
to retreive the calculation that i will need.

Unfortunately, I can't easily create ColumnE because my pivot table shows
the following:

ColumnA ColumnB ColumnC ColumnD ColumnE
Person1 House1 Item1 Calculation
Person1.House1
Item2
Calculation
House2 Item3 Calculation
..House2
Item4
Calculation
Person2 House3 Item1 Calculation
Person2.House3

As you can see I have empty spaces, not created because of absence of data,
but because Excel joins the similar data (because I put both the data
entered as ColumnA and ColumnB as rows).

Do you know how can I make ColumnE look as I need?

I've thought that there may be any option in the pivot table to make it not
create any empty space. Other option is to make ColumnE look for the first
value in ColumnA and ColumnB backwards.

Thanks in advance.


PD: Sorry for the bad subject in the preceding post.

 

[Bernie Deitrick]

Fambrius,

One way is to create a new table of formulas.

Let's say that your PT starts in Cell A4 (firts of your header values). Then in cell H4, enter the
formula

=A4

and copy this to the right for as many columns as you have. (Note that you should NOT type = then
click cell A3, or you will get the PTData function, which you don't want)

Then in cell H5, enter the formula

=IF(A5="",H4,A5)

and copy that down and to the right to match your pivot table. Then you can do your calcs of that
table.

HTH,
Bernie
MS Excel MVP

 

[Fambrius]

Thank you very much, it works!!!!!!!

Nice idea