help with resume from pivot table

[mario rdz]

Dear Ninjas.
I'm doing an extraction from a pivot table, this is for my main graph for a dashboard..

I'm need to get the $ from the last 3 years, for each month and for certain material groups..

The problem comes when I filter a specific group with no spend $$ in a certain year..
The pivot table collapses so in my resume table I loose from wich column I need to get the data..

how can I condition this ??

I appreciate your help

 

[srinidhi]

Uploading the file will help us to solve your problem.

 

[mario rdz]

Dear srinidhi.

Here is an example file,.. thanks

 

[coolsac12]

Dear srinidhi.

Here is an example file,.. thanks

Hi @mario rdz,

Please find the attached sheet.... you need to put the given formula.

=IFERROR(OFFSET($A$21,MATCH($A37,$A$22:$A$33,0),MATCH(B$36,$B$20:$F$20,0)),"-")

Thanks

 

[mario rdz]

Dear coolsac12

That is exactly what I was looking for,,, thanks a lot...

Just 1 more thing if possible:

What if instead of get the Spend $$ .. I want to get the PO lines ??
The PO line is right beside the $$ for each year-month..

Reagards
Mario

 

[coolsac12]

Dear coolsac12

That is exactly what I was looking for,,, thanks a lot...

Just 1 more thing if possible:

What if instead of get the Spend $$ .. I want to get the PO lines ??
The PO line is right beside the $$ for each year-month..

Reagards
Mario

Hi @mario rdz ,

Please use the given formula with Ctrl+Shift+Enter

=IFERROR(INDEX($B$22:$G$33,MATCH($A37,$A$22:$A$33,0),MATCH(B$35&B$36,$B$35:$G$35&$B$21:$G$21,0)),"-")

I have attached the sheet,

Thanks

 

[mario rdz]

Hi coolsac12

I have applied the fixes,, thanks a lot for your help !!!

Saludos