Need Help With Formula Retrieving Data from Pivot-Table Based On Column and Row Name

P

psmi123

New Member
Joined
Jul 10, 2018
Messages
16
There are 3 Sheets in this file:"Mapping", "Data", and "Pivot"

1st Sheet is "Mapping"


LevelNameName Sector IDCompany Sector IDSectorApplicability IDLinkLink NameSolution NameMapping 1Mapping 1 RevenueMapping 2Mapping 2 Revenue
1Dairy Farms43523D43523DairyD43523Dairy(blank)01-Dairy43523:airy'C4.2.0.0.0.0C4.3.0.0.0.0:TEST
1Dairy Farms52236D52236DairyD52236Dairy(blank)01-Dairy52236:airy'C4.2.0.0.0.0C4.3.0.0.0.0:TEST
1Dairy Farms74245D74245DairyD74245Dairy(blank)01-Dairy74245:airy'C4.2.0.0.0.0-
1Burger Deluxe27434B27434BurgerB27434Burger(blank)01-Burger27434:urger'C4.2.0.0.0.0-
1Burger Deluxe97537B97537BurgerB97537Burger(blank)01-Burger97537:urger'C4.2.0.0.0.0-
1Chicken Dreams35786C35786ChickenC35786Chicken(blank)01-Chicken35786:hicken'C4.2.0.0.0.0-
1Chicken Dreams85357C85357ChickenC85357Chicken(blank)01-Chicken85357:hicken'C4.2.0.0.0.0-
1Chicken Dreams38535C38535ChickenC38535Chicken(blank)01-Chicken38535:hicken'C4.2.0.0.0.0C4.3.0.0.0.0:TEST

<tbody>
</tbody>


2nd Sheet is "Data"


CountryCountryZoneExColumn1ExColumn2ExColumn3ExColumn4ExColumn5SectorExColumn6ExColumn7ExColumn8ExColumn9ExColumn10ExColumn11ExColumn12ExColumn13Product IdExColumn14ExColumn15ExColumn16ExColumn17RevenueRecordsDomainDomain Sub 1Domain Sub 2Domain Sub 3Domain Sub 4Domain Sub 5Domain Sub 6
USAUSA1DairyD2435$251C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
USAUSA2DairyF2435$3452C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
USAUSA3DairyT2436$621C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
USAUSA4BurgerD2436$8721C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
ItalyItaly1BurgerF2436$351C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
ItalyItaly2ChickenT2437$3752C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
ItalyItaly3ChickenD2437$261C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
ItalyItaly4ChickenF2437$731C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
ItalyItaly5DairyT2438$844C4.2.0.0.0.0:ICEC4C4.2C4.2.0C4.2.0.0C4.2.0.0.0C4.2.0.0.0.0
ItalyItaly6DairyD2438$972C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
GermanyGermany1DairyF2438$582C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
GermanyGermany2BurgerT2439$124C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
GermanyGermany3BurgerD2439$422C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
GermanyGermany4ChickenF2439$262C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
FranceFrance1ChickenT2440$732C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
FranceFrance2ChickenD2440$575C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
FranceFrance3DairyF2440$844C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
FranceFrance4DairyT2441$342C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0
FranceFrance5DairyD2441$841C4.3.0.0.0.0:TESTC4C4.3C4.3.0C4.3.0.0C4.3.0.0.0C4.3.0.0.0.0

<tbody>
</tbody>


The 3rd Sheet is the Pivot Table created from the "Data" Sheet


Sum of RevenueColumn Labels
Row Labels BurgerChickenDairyGrand Total
C4.2.0.0.0.0:ICE9074745161897
C49074745161897
C4.29074745161897
C4.2.09074745161897
C4.2.0.09074745161897
C4.2.0.0.09074745161897
C4.2.0.0.0.09074745161897
C4.3.0.0.0.0:TEST54156357567
C454156357567
C4.354156357567
C4.3.054156357567
C4.3.0.054156357567
C4.3.0.0.054156357567
C4.3.0.0.0.054156357567
Grand Total9616308732464

<tbody>
</tbody>

Look at Sheet 1 in row K "Mapping 1 Revenue". I want to create a formula that takes the Column E "Sector" and Column J "Mapping 1" and searches for it in the Pivot Table. I want to find the Mapping 1 revenue based on Sector and Mapping ID and I need a formula that searches the pivot table based on that same column and row. If there are any other questions I can answer, please let me know. Thank you in advance for the help!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

J
  • Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
269
johnnyL
J

Forum statistics

Threads
1,215,429
Messages
6,124,839
Members
449,193
Latest member
MikeVol

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top