Extracting info from Pivot table

Hello!

I have a pivot table that shows revenue against each department per client. With the help of this information, I want to pull up against the name of the client which departments they are using - what function can i use? many thanks

Without any indication of how your data is organised its difficult to say which is the best way to go. By the sound of it department is the top level in your pivot table, with client below that.
I would suggest creating another pivot table with client at the top and department below it. If workable with your other processes, you could amend your existing table instead of creating another one.

Thanks for your reply!

The pivot table is arranged with client on top and then departments with the revenues against them. I cant use the pivot table itself because for my report i am using other data from other documents. I'm looking for a function that can pull up in one cell all the departments against a certain client. I tried to use an a combination of INDEX, IF and Rows but it is generating a value error

reesespieces said:

I'm looking for a function that can pull up in one cell all the departments against a certain client.

Click to expand...

I am a little puzzled, as if you have access to enough detailed data that will let you construct a formula/function then you should be able to "pivot" it, but I can't see your data so perhaps I'm missing something
If you want the result returned in one cell however, then this must be a single total for all departments involving the selected Client?

So if your data is like this: (Lets say there are 1000 records)
Col A Department
Col B Client Code
Col C Value

Try this in a spare cell: =SUMIF(B2:B1001, "=Smith001", C2:C1001)

Thank you. I couldnt use the pivot table for several reasons but I finally figured it out however now I am struggling to drag this formula down as it is an array formula:

in Cell a11 =INDEX($A$2:$B$8,SMALL(IF($A$2:$A$8=$A$11,ROW($A$2:$A$8)),COLUMN(A1))-1,2)

IF i try to drag this down a11 remains the same rather than changing to a12. Instead COLUMN(A1) changes which i need to stay the same

any thoughts?

thank you!

When using formulas to extract data from PT's, the results could change each time the source data is changed
Perhaps post a small sample sheet with some data showing desired results.

Couldn't you use slicers eventually?

Move your formula away from A11 is there is a circular Ref problem.
Also, Amend to COLUMNS($A$1:A$1) if copying formula across
or Amend to ROWS($A$2:$A2) if copying down. Dont worry about these changing when copied... they are supposed to
Dont forget CONTROL+SHIFT+ENTER after amending/inputting the formula not just ENTER.
I would also put IF() before SMALL() so as to return (eg) "" when listing exceeded instead of #NUM!
Do use Evaluate Formula to understand what its doing.