Search value on pivot table

[elbarauljr]

Hello All,

I have a vlookup formula (looking Project ID) to produce a report from a pivot table. The report have 12 columns (YY-MM format) and the values will be zero if not found in the pivot so everything works when the pivot shows all 12 columns.
The problem start when the pivot does not show all columns, for example is there is no data for "20-01" then the pivot will remove that column and the report will show incorrect numbers on some columns.

Attached is a sample of the situation. Is there is a formula to use in the report where I can get the value looking for "Project ID", but also by the column title (example "19-11") in the pivot?

Thank you.

[Bo_Ry]

Please try at C4
=SUMIFS($AL$23:$AL$138,$Q$23:$Q$138,LEFT($A4,9),$AU$23:$AU$138,TEXT(MID($A4,11,9),"0;;;<>\No"),$AS$23:$AS$138,C$3)

[elbarauljr]

Thank you Bo_Ry,

Your formula works by connecting the report and the data source, but the data source is actually on another file with over 500k rows so I prefer to create the report out of the pivot table (both on the same spreadsheet).
Is there a formula to use in the report to search values in the pivot based on the "Project ID" and "columns with 19-10, 19-11, etc.."

The tricky part would be to show the correct value let's say 10,875 for Project ID "C77814000 3001800" and column "20-06" when the pivot remove for example column "20-05".

Thank you.

[JeteMc]

Try pasting the following into cell C3 and copying across to cell N3:

Please Login or Register  to view this content.

Let us know if you have any questions.

[elbarauljr]

JeteMc,

I need to show all headers in the report from "19-10" to "20-09" even if some are not showing in the pivot table. The formula I am looking in the report would be more to search the pivot table like this: If Project ID (A5) is "C77814000 3001800" and YY-MM (K3) is "20-06" then value is 10,875 otherwise zero.

To show the correct value, it would need to meet both criteria; Project ID and YY-MM in the pivot table. This way I can leave the headers in the report intact.

Thanks you.

[JeteMc]

Try pasting the following into cell C4 and then copying across and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[elbarauljr]

It works! Now the pivot automatically refresh / hide columns per data source and the report shows correct values (other than zero) under the correct "YY-MM" column.
The actual source, pivot and report are much larger than this sample, but can apply the same concept.

Thank you so much.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.