Using the information from a pivot table

[Louja]

Hi there,

I was wondering if anyone would be kind enough to help.

I have a report that I receive daily and need to total month to date
lots by 2 fields which are ledger code and instrument code.

I then need to use the information from the pivot to do some more
calculations as its the month to date information which is important.
There is an extract of the pivot below:

Sum of Total Lots (Cleared)
Instrument Code Ledger Code(Query 1 with Trading) Total
C 0TCP1 4
0TDC1 8
C Total 12
CBO 0TAH1 196
0TJW1 10
0TPO1 24
CBO Total 230

I need to be able to do Vlookups etc on both the instrument code and
ledger code for each line of the report. Is there anyway of doing
this as the report is going to change daily so want to make it as
automated as possible.

Thanks in advance

 

[Lori]

Suppose your table range is A1:C9 and your lookup values are in E3:F3.

You need to fill in the blanks below the data in the lookup formulas.
One way to do this is to replace A3:A9 in your formula by:
LOOKUP(ROW(A3:A9),ROW(A3:A9)/(A3:A9<>""),A3:A9)

If there were no blanks in the range you could use:
=LOOKUP(2,1/(B3:B9=F3)/(A3:A9=E3),C3:C9)

If there are blanks in column A make the replacement above to get:
=LOOKUP(2,1/(B3:B9=F3)/(LOOKUP(ROW(A3:A9),ROW(A3:A9)/(A3:A9<>""),A3:A9)=E3),C3:C9)

eg: E3="C",F3="0TDC1" returns 8.

 

[Louja]

Suppose your table range is A1:C9 and your lookup values are in E3:F3.  

You need to fill in the blanks below the data in the lookup formulas.
One way to do this is to replace A3:A9 in your formula by:
LOOKUP(ROW(A3:A9),ROW(A3:A9)/(A3:A9<>""),A3:A9)

If there were no blanks in the range you could use:
=LOOKUP(2,1/(B3:B9=F3)/(A3:A9=E3),C3:C9)

If there are blanks in column A make the replacement above to get:
=LOOKUP(2,1/(B3:B9=F3)/(LOOKUP(ROW(A3:A9),ROW(A3:A9)/(A3:A9<>""),A3:A9)=E3) ,C3:C9)

eg: E3="C",F3="0TDC1" returns 8.

But if the blanks are going to vary on a day to day basis would this
work. The pivot table is big and I think this way might be too manual
as there are lots of lookups etc etc.

Thanks

 

[Lori]

Did you try it? Normal lookups won't work because of the gaps in the data.
This method is general and can be extended to any number of columns
by replacing column references by the lookup formulas shown. You need
to make the ranges long enough to allow for more data after refresh
however and it does make for long formulas,

A simpler alternative for returning values in the data region of the table
is GetPivotData. You can create this by pressing = and clicking a cell in
the table and then editing the criteria. This agrees with the result above:

=GETPIVOTDATA("Total Lots (Cleared)",$A$1,"Instrument Code",E3,
"Ledger Code(Query 1 with Trading)",F3)