Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table Lookup Grid
Hi I am looking for a way to lookup a particular cell within a pivot table the pivot table contains 8000 account numbers down the left hand side and the Months accross the top and sales in the grid. Am am wanting to put the account number and Month into a cell and pull through the sales for that month i am also looking to then show the previous and next 3 months sales in the previous and next 3 cells on the same row, Smaple of what i want below but i cant find the right formula. Other problem is that the pivot table is built of a server and the account numbers are not sequential Any Ideas Month Aug-05 Account No -3 -2 -1 Aug-05 +1 +2 +3 13758 12547 12357 12369 14789 36541 25487 Cheers Al -- MIVELD ------------------------------------------------------------------------ MIVELD's Profile: http://www.excelforum.com/member.php...o&userid=24562 View this thread: http://www.excelforum.com/showthread...hreadid=467097 |
#2
|
|||
|
|||
The logic will be
=INDEX(PIVOTTABLE,MATCH(Company,PTColumn,False),MA TCH(Date,PTHeader,False)) =INDEX(PIVOTTABLE,MATCH(Company,PTColumn,False),MA TCH(Date,PTHeader,False)+1) =INDEX(PIVOTTABLE,MATCH(Company,PTColumn,False),MA TCH(Date,PTHeader,False)+2) =INDEX(PIVOTTABLE,MATCH(Company,PTColumn,False),MA TCH(Date,PTHeader,False)+3) =INDEX(PIVOTTABLE,MATCH(Company,PTColumn,False),MA TCH(Date,PTHeader,False)-1) =INDEX(PIVOTTABLE,MATCH(Company,PTColumn,False),MA TCH(Date,PTHeader,False)-2) =INDEX(PIVOTTABLE,MATCH(Company,PTColumn,False),MA TCH(Date,PTHeader,False)-3) The specifics depend on the format of the PT headers. -- HTH, Bernie MS Excel MVP "MIVELD" wrote in message ... Hi I am looking for a way to lookup a particular cell within a pivot table the pivot table contains 8000 account numbers down the left hand side and the Months accross the top and sales in the grid. Am am wanting to put the account number and Month into a cell and pull through the sales for that month i am also looking to then show the previous and next 3 months sales in the previous and next 3 cells on the same row, Smaple of what i want below but i cant find the right formula. Other problem is that the pivot table is built of a server and the account numbers are not sequential Any Ideas Month Aug-05 Account No -3 -2 -1 Aug-05 +1 +2 +3 13758 12547 12357 12369 14789 36541 25487 Cheers Al -- MIVELD ------------------------------------------------------------------------ MIVELD's Profile: http://www.excelforum.com/member.php...o&userid=24562 View this thread: http://www.excelforum.com/showthread...hreadid=467097 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Can I use a 'lookup' to a Pivot Table? | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |