Copying formulas that draw data from a pivot table

[saraapple]

I need to use a simple formula to work percentage share - but as the data is being taken from a pivot table I am struggling to copy my formulas and there is too much data to do it individually.

This is the formula I want to copy:
MONDAY %
=GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1,"Store ID",121,"Day of week","Monday","Region","East")/GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1)

TUESDAY %
When I drag the formula across I want it to say:
=GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1,"Store ID",121,"Day of week","Tuesday","Region","East")/GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1)

I also need to copy the formulas down:

STORE 121
=GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1,"Store ID",121,"Day of week","Monday","Region","East")/GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1)

STORE 185
=GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1,"Store ID",185,"Day of week","Monday","Region","East")/GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$4,"Week",1)

Is there a quick way to do this?

Alternatively - could I create a pivot table that would display percentages so I would not need a separate table?

Budgeting Tool 070820.xlsx
	A	B	C	D	E	F	G
4	Sum of RPE	Column Labels
5		1					1 Total
6	Week	Monday	Tuesday	Wednesday	Thursday	Friday
7	East
8	121	17.595	15.715	9.885	8.717	6.172	58.084
9	185	6.338	14.926	13.547	9.383	5.801	49.995
10	225	8.26	5.757	5.962	15.801	9.754	45.534
11	385	16.052	12.589	9.091	7.717	6.216	51.665
12	535	4.716	7.005	18.885	14.713	3.922	49.241
13	550	5.465	18.26	16.381	8.759	6.173	55.038
14	580	4.965	5.004	15.049	10.713	4.588	40.319
15	625	14.969	13.589	8.466	7.634	4.794	49.452
16	740	12.009	10.839	9.508	6.965	6.212	45.533
17	805	3.923	8.003	14.68	18.719	6.088	51.413
18	875	8.175	8.968	13.221	24.598	13.884	68.846
19	900	5.091	7.841	16.513	17.177	5.796	52.418
20	910	11.428	22.933	23.346	7.672	6.257	71.636
21	995	15.009	10.673	10.008	9.72	6.505	51.915
22	Grand Total	133.995	162.102	184.542	168.288	92.162	741.089
Deliveries Pivot

On the sample data I have provided above I need to take B8 and divide by G22 to get a percentage of 2.374%.

I am really hoping someone can help me out - as there is too much data for me to type each cell!

Thanks
Sara

 

[COwen]

You could use a for/next loop to cycle through each cell. I do not have a computer right now, so I cannot write you the code. But if you’re familiar with VBA, that’ll get you on the right track. Otherwise, I can guide you on what to try.

 

[COwen]

As for your percentages, =B8/$G$22

 

[saraapple]

Thank you for your reply
From your answer I take it that VBA code is the best way to approach this, I did think a pivot might be able to give me the results I needed but after spending hours trying to do this I gave up!
I have created work with VBA before - but my knowledge is very limited (I still use the "record macro") so any advice you could give me will be most appreciated.
Sara

 

[COwen]

Thank you for your reply
From your answer I take it that VBA code is the best way to approach this, I did think a pivot might be able to give me the results I needed but after spending hours trying to do this I gave up!
I have created work with VBA before - but my knowledge is very limited (I still use the "record macro") so any advice you could give me will be most appreciated.
Sara

The first two formulas, where are they going? What cells? And the second two as well, please.

 

[saraapple]

The first formula is in B4

Budgeting Tool 070820.xlsx
	A	B	C	D	E	F	G	H	I
1		1							2
2	Week	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday
3	East
4	121	1.150%	0.851%	0.117%	0.103%	0.073%	0.000%	0.000%	0.209%
5	185	0.414%	0.177%	0.160%	0.111%	0.069%	0.000%	0.000%	0.101%
6	225	0.098%	0.068%	0.071%	0.187%	0.115%	0.000%	0.000%	0.061%
7	385	0.190%	0.149%	0.108%	0.091%	0.074%	0.000%	0.000%	0.198%
8	535	0.056%	0.083%	0.224%	0.174%	0.046%	0.000%	0.000%	0.095%
9	550	0.065%	0.216%	0.194%	0.104%	0.073%	0.000%	0.000%	0.086%
10	580	0.059%	0.059%	0.178%	0.127%	0.054%	0.000%	0.000%	0.059%
11	625	0.177%	0.161%	0.100%	0.090%	0.057%	0.000%	0.000%	0.194%
12	740	0.142%	0.128%	0.113%	0.082%	0.074%	0.000%	0.000%	0.192%
13	805	0.046%	0.095%	0.174%	0.222%	0.072%	0.000%	0.000%	0.070%
14	875	0.097%	0.106%	0.157%	0.291%	0.164%	0.000%	0.000%	0.066%
15	900	0.060%	0.093%	0.195%	0.203%	0.069%	0.000%	0.000%	0.070%
16	910	0.135%	0.271%	0.276%	0.091%	0.074%	0.000%	0.000%	0.093%
17	995	0.178%	0.126%	0.118%	0.115%	0.077%	0.000%	0.000%	0.184%
18	Ireland
19	645	0.023%	0.044%	0.192%	0.062%	0.178%	0.000%	0.000%	0.019%
20	750	0.016%	0.050%	0.125%	0.066%	0.138%	0.000%	0.000%	0.047%
21	850	0.022%	0.087%	0.148%	0.091%	0.190%	0.000%	0.000%	0.027%
22	880	0.021%	0.057%	0.110%	0.073%	0.172%	0.000%	0.000%	0.022%
23	905	0.038%	0.043%	0.119%	0.075%	0.212%	0.000%	0.000%	0.023%
24	Offshore
25	114	0.096%	0.215%	0.125%	0.074%	0.074%	0.000%	0.000%	0.054%
26	615	0.072%	0.078%	0.059%	0.149%	0.098%	0.000%	0.000%	0.048%
27	London
28	25	0.022%	0.137%	0.125%	0.186%	0.079%	0.000%	0.000%	0.078%
29	45	0.038%	0.160%	0.168%	0.094%	0.041%	0.000%	0.000%	0.056%
30	112	0.071%	0.191%	0.143%	0.089%	0.061%	0.000%	0.000%	0.074%
31	115	0.133%	0.110%	0.084%	0.085%	0.145%	0.000%	0.000%	0.118%
32	175	0.146%	0.102%	0.079%	0.113%	0.143%	0.000%	0.000%	0.126%
33	335	0.143%	0.165%	0.106%	0.140%	0.184%	0.000%	0.000%	0.161%
34	455	0.098%	0.083%	0.046%	0.197%	0.122%	0.000%	0.000%	0.080%
35	640	0.110%	0.112%	0.123%	0.278%	0.160%	0.000%	0.000%	0.058%
36	680	0.147%	0.262%	0.228%	0.113%	0.081%	0.000%	0.000%	0.109%
37	725	0.105%	0.091%	0.142%	0.280%	0.172%	0.000%	0.000%	0.079%
38	765	0.082%	0.241%	0.166%	0.084%	0.073%	0.000%	0.000%	0.089%
39	825	0.082%	0.074%	0.043%	0.236%	0.114%	0.000%	0.000%	0.048%
40	Midlands
41	10	0.135%	0.159%	0.104%	0.072%	0.052%	0.000%	0.000%	0.109%
42	35	0.063%	0.156%	0.227%	0.097%	0.049%	0.000%	0.000%	0.078%
43	55	0.138%	0.129%	0.111%	0.076%	0.049%	0.000%	0.000%	0.170%
44	80	0.069%	0.169%	0.169%	0.109%	0.059%	0.000%	0.000%	0.143%
Percentage Del

Cell Formulas
Range	Formula
B4	B4	=GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$1,"Week",1,"Store ID",121,"Day of week","Monday","Region","East")/GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$1,"Week",1)
C4	C4	=GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$1,"Week",1,"Store ID",121,"Day of week","Tuesday","Region","East")/GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$1,"Week",1)
B41:H44,B28:H39,B25:H26,B19:H23,B6:H17,C5:H5,D4:H4	D4	='Historical Figs Del'!D4/'Historical Figs Del'!$I$191
I41:I44,I28:I39,I25:I26,I19:I23,I4:I17	I4	='Historical Figs Del'!J4/'Historical Figs Del'!$Q$191
B5	B5	=GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$1,"Week",1,"Store ID",185,"Day of week","Monday","Region","East")/GETPIVOTDATA("RPE",'Deliveries Pivot'!$A$1,"Week",1)

 

[COwen]

With my phone, this is really difficult. @Fluff has always been able to help me with code. He may even have a better solution. If he isn’t able to help you do to time, I will work on something that should work.

 

[saraapple]

Sorry to ask again - but have you any guidance for me on this? As much as I would like the problem is not going away and I need to get this done ASAP.
thanks know you must be busy appreciate any advise.
Sara