• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Database Question

P

PaulFogel123

Member
Hello,

The attached file has a simple Pivot Table on top and a blank table below that. I need to capture the correct values from the Pivot Table and put them in the bottom table. Both the Pivot Table and the table below will be refreshed each month. The dates between them may not match, so I need the formulas in the bottom table to find what it needs from the Pivot Table. If it can't find a match, then a zero would suffice.

I don't know if an INDEX/MATCH is the answer, or if there is some better way. Thanks for your help!

Paul
 

Attachments

  • PaulsTestWorkbook.xlsx
    132.7 KB · Views: 3
Why not filter the Pivot itself?

Date filter on Column Labels After:12/31/2015.

Since pivots can often be changed (filter etc) by users. INDEX/MATCH and other formulas are not recommended.

If you must use formula. Use GETPIVOTDATA function.

See attached sample. You can remove filter and see that it retains info.

I'm not too experienced in it... but if data is from powerpivot you may be able to use CUBEVALUE function as well. I believe steps are...
1. Select pivot table, go to PivotTable Tools
2. OLAP Tools -> Converttoformulas

There you will see all of pivot converted to CUBE formulas, check syntax and formula construction and use that to work with data.
 

Attachments

  • PaulsTestWorkbook.xlsx
    127.1 KB · Views: 3
Ah, got it to work.

For an example....
Cube formula for May-15, Medicare Inpatient Gross Revenue would be....

=CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of Medicare Inpatient Gross Revenue]"),CUBEMEMBER("ThisWorkbookDataModel",{"[Actuals].[Month].&[2015-05-31T00:00:00]","[Actuals].[Data Load Complete].&[True]"}))

Edit: so to get Jan-16, Medicare Inpatient Gross Revenue you change 2015-05-31 portion to 2016-01-31.
 
Last edited:
Thanks for your expert reply! However, this will be in Excel Online, which does not support Power Query or Power Pivot. It will have to be a standard Pivot for now. Also, the users will not have access to the Pivot Table and won't be able to change it. I should also mention that the bottom table will reside in a different file. Does this change what the solution should be?
 
Yes. In that case, INDEX/Match/Match will work just fine.

=INDEX($B$4:$P$6,MATCH($A11,$A$4:$A$6,0),MATCH(B$10,$B$2:$P$2,0))

Extend Column range as needed.

Edit: Nest it in IFERROR(,"") to blank out if you want to set up extended range ahead of time.
 
Last edited:
You must log in or register to reply here.
Back
Top