Formula needed to return specific value from a pivot table

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
128
Office Version
  1. 2010
Platform
  1. Windows
I am really struggling with this and hope someone can help me.
I have a pivot table looking at the percentage share of a variable and I need to bring the specific value (as a %) into another excel tab.
Here is my pivot:
13.05.2020.xlsx
ABCDEFGHIJK
4Sum of RPEColumn Labels
5Week12345678910
6100.51%0.37%0.41%0.43%0.36%0.38%0.35%0.36%0.35%0.41%
7Midlands
8Monday0.13%0.10%0.14%0.11%0.08%0.20%0.06%0.09%0.00%0.05%
9Tuesday0.15%0.12%0.10%0.11%0.09%0.07%0.10%0.10%0.05%0.19%
10Wednesday0.10%0.04%0.06%0.04%0.03%0.03%0.05%0.04%0.10%0.08%
11Thursday0.07%0.05%0.06%0.17%0.08%0.04%0.08%0.05%0.06%0.00%
12Friday0.05%0.06%0.06%0.00%0.07%0.04%0.07%0.08%0.06%0.09%
13Saturday0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.09%0.00%
% Split Deliveries


I need my formula to look at the Store (A6) the week number, and then the day of the week.

Here is the table I am trying to input the formula in:
13.05.2020.xlsx
ABCDE
1RPE:Depot:NorthTotal RPE:1000
230Week:1Select Week number
3Day of week:MondaySelect Day of Week
4RegionMidlands Select Region
6Store IDStore NameSum of RPERPE %Region
710Birmingham11.383Midlands
8650Burton10.674Midlands
9305Cannock TAIL LIFT7.883Midlands
10815Cheltenham5.964Midlands
11790Crewe5.963Midlands
12655Derby New12.26Midlands
13920Erdington13.048Midlands
14560Fenton15.384Midlands
15810Gloucester8.424Midlands
16775Hereford5.968Midlands
17610Ilkeston12.216Midlands
18685Kidderminster - NDS15.716Midlands
19205Loughborough TAIL LIFT2.419Midlands
20360Oldbury tail lift7.843Midlands
2155Redditch TAIL LIFT11.675Midlands
22160Rugby TAIL LIFT2.752Midlands
2380Shrewsbury Sundorne5.797Midlands
2435Solihull TAIL LIFT5.34Midlands
25545Stafford TAIL LIFT13.426Midlands
26495Telford NDS9.637Midlands
27510Walsall10.549Midlands
28590Wolverhampton18.428Midlands
29935Worcester6.337Midlands
30Grand Total219.086
Forecaster - Deliveries
Cell Formulas
RangeFormula
A6:A29A6=VLOOKUP(B6,Deliveries!E:F,2,FALSE)
E7:E29E7=VLOOKUP(A7,'Stores split by region'!A:D,4,FALSE)


So I want my formula in cell D7 and I want it to look at C1 & C2 and then A7 so it would return the result 0.13%

I am sure this can be done with an index and match formula but for the life of me I cant get it to work.
any help greatly appreciated.

Thank you
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,835
Office Version
  1. 2010
Platform
  1. Windows
Thank you for your help again but I am struggling to make it work. The first store number I need to check against is in A7 but when I change your C5 to A7 I get an #N/A - any ideas?
I had 35 in C5 and the formula returned 0.06%. When I replaced C5 with A7, the formula returned 0.13%. Not sure why it didn't work for you.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,870
Messages
5,627,362
Members
416,245
Latest member
Xterminat

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
Top