+ Reply to Thread
Results 1 to 8 of 8

Search value on pivot table

  1. #1
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    15

    Search value on pivot table

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,969

    Re: Search value on pivot table

    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)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    15

    Re: Search value on pivot table

    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.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,238

    Re: Search value on pivot table

    Try pasting the following into cell C3 and copying across to cell N3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    15

    Re: Search value on pivot table

    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.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,238

    Re: Search value on pivot table

    Try pasting the following into cell C4 and then copying across and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    15

    Re: Search value on pivot table

    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.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,238

    Re: Search value on pivot table

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table Search options
    By milo1984 in forum Excel General
    Replies: 1
    Last Post: 03-20-2018, 08:28 PM
  2. Pivot table search- need help!
    By thursday140 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2016, 10:05 AM
  3. A macro that search info from a pivot table
    By natatxu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-26-2016, 10:00 AM
  4. Search option to control Pivot Table
    By narenorchid in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-03-2014, 09:01 AM
  5. search box or pivot table?
    By lingga.47 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-31-2013, 07:41 AM
  6. Let pivot table search for last row in column
    By Turbobiker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-29-2009, 07:46 AM
  7. Search function within Pivot Table
    By firefluff in forum Excel General
    Replies: 0
    Last Post: 03-08-2005, 12:27 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1