Automate Copy Paste Formulas One Column Over and Hard Code Values In Original Column (w/ Mini Sheet)

M

Mundash

New Member
Joined
Jul 26, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Anybody know how to automate the process of copying formulas from one column to the next, then hard coding the values on the original column?

Each month it will move one column over (as each month represents a new column), the formulas will overwrite existing hard coded values in the new column. I tried recording a macro but it does not work since it is one column over each month. Any ideas? Thank you in advance!

Sample Data File_7.26.22.xlsx
ABCDEFGHIJKLMNOP
1
2
3Hard CodedHard CodedHard CodedHard CodedHard CodedFormulaNew month column overwrite hard coded values with formulaEach month the formulas move over one month Hard CodedHard CodedHard CodedHard CodedHard Coded
4123456789101112
7Product #1Jan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22
8Product 1 Order Order5,3714,4125,1794,9874,7953,5505,3714,4125,1794,9874,7954,987
9 AA+AS At SeaShipping6437062282,0682,1832,1736437062282,0682,1832,068
10IT+TN In-TransitTransit8082,6111,2518041,8451,3108082,6111,2518041,845804
11PAPortWarehouse #12493451921,3104801802493451921,3104801,310
12PSStockWarehouse #236174427620315914361744276203159203
13Sum of MTLY WHSLWhStore Rtl Sales2,8483,0635,3024,5695,9605002,8483,0635,3024,5695,9604,569
14Sum of MTLY WHSLWhStore B2B420202070042020202
15Sum of MTLY WHSLWhStore Emp. Purch0010150200001015015
16Sum of MTLY RTLRtStore Rtl Sales3,1322,8665,4254,2336,1534003,1322,8665,4254,2336,1534,233
17Sum of MTLY RTLRtStore B2B01,05702080001,0570202
18Sum of MTLY RTLRtStore Emp. Purch200250135120275100200250135120275120
19Sum of Dealer StockRtl Sales458656533869675300458656533869675869
20Sum of Dealer StockStockB2B1,0550002119001,0550002110
21Sum of Dealer StockEmp. Purch6626126006626126
22
23Product #2Jan-22Feb-22Mar-22Apr-22May-22Jun-22
24
25Product 2 Production Order15,72918,79823,01828,7703,4371,25615,72918,79823,01828,7703,43723,018
26 AA+AS WaterShipping5,5723,1992,0166,1738,7531,7905,5723,1992,0166,1738,7532,016
27IT+TNTransitTransit6,2348,1186,7522,5096,2638996,2348,1186,7522,5096,2636,752
28PAWarehouse #1Warehouse #11,8871,7341,3771,8951,4564211,8871,7341,3771,8951,4561,377
29PSWarehouse #2Warehouse #28691,6983,4101,922568408691,6983,4101,9225683,410
30Sum of MTLY WHSLWhStore Rtl Sales9,10715,23216,44817,36515,7735009,10715,23216,44817,36515,77316,448
31Sum of MTLY WHSLWhStore B2B1,9451,5361,4234056757001,9451,5361,4234056751,423
32Sum of MTLY WHSLWhStore Emp. Purch6210384200621038410
33Sum of MTLY RTLRtStore Rtl Sales10,44813,37317,71217,41716,30440010,44813,37317,71217,41716,30417,712
34Sum of MTLY RTLRtStore B2B5752,2317121,3471,3438005752,2317121,3471,343712
35Sum of MTLY RTLRtStore Emp. Purch21283121100212831218
36Sum of Dealer StockRtl Sales1,8343,6922,4282,3771,8453001,8343,6922,4282,3771,8452,428
37Sum of Dealer StockStockB2B2,2311,6752,5801,6369739002,2311,6752,5801,6369732,580
38Sum of Dealer StockEmp. Purch1710215136001710215132
39
22CY RDOWN
Cell Formulas
RangeFormula
I4:O4I4=+H4+1
I8,I25I8=INDEX(CY!$D$6:$O$30,MATCH($A8,CY!$B$6:$B$30,0),MATCH(I$4,CY!$D$4:$O$4,0))
I9,I26I9=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A9,Top!$L$3:$O$3,0))
I10,I27I10=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A10,Top!$L$3:$O$3,0))
I11,I28I11=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A11,Top!$L$3:$O$3,0))
I12,I29I12=INDEX(Top!$L$4:$O$30,MATCH($A8,Top!$J$4:$J$30,0),MATCH($A12,Top!$L$3:$O$3,0))
I13,I30I13=INDEX('Bottom (CY)'!$D$7:$F$38,MATCH($A8,'Bottom (CY)'!$A$7:$A$38,0),MATCH($A13,'Bottom (CY)'!$D$6:$F$6,0))
I14,I31I14=INDEX('Bottom (CY)'!$D$90:$F$114,MATCH($A8,'Bottom (CY)'!$A$90:$A$114,0),MATCH($A14,'Bottom (CY)'!$D$89:$F$89,0))
I15,I32I15=INDEX('Bottom (CY)'!$D$45:$F$82,MATCH($A8,'Bottom (CY)'!$A$45:$A$82,0),MATCH($A15,'Bottom (CY)'!$D$44:$F$44,0))
I16,I33I16=INDEX('Bottom (CY)'!$D$7:$F$38,MATCH($A8,'Bottom (CY)'!$A$7:$A$38,0),MATCH($A16,'Bottom (CY)'!$D$6:$F$6,0))
I17,I34I17=INDEX('Bottom (CY)'!$D$90:$F$114,MATCH($A8,'Bottom (CY)'!$A$90:$A$114,0),MATCH($A17,'Bottom (CY)'!$D$89:$F$89,0))
I18,I35I18=INDEX('Bottom (CY)'!$D$45:$F$82,MATCH($A8,'Bottom (CY)'!$A$45:$A$82,0),MATCH($A18,'Bottom (CY)'!$D$44:$F$44,0))
I19,I36I19=INDEX('Bottom (CY)'!$D$7:$F$38,MATCH($A8,'Bottom (CY)'!$A$7:$A$38,0),MATCH($A19,'Bottom (CY)'!$D$6:$F$6,0))
I20,I37I20=INDEX('Bottom (CY)'!$D$90:$F$114,MATCH($A8,'Bottom (CY)'!$A$90:$A$114,0),MATCH($A20,'Bottom (CY)'!$D$89:$F$89,0))
I21,I38I21=INDEX('Bottom (CY)'!$D$45:$F$82,MATCH($A8,'Bottom (CY)'!$A$45:$A$82,0),MATCH($A21,'Bottom (CY)'!$D$44:$F$44,0))
I23I23=I$5
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
You must log in or register to reply here.

Similar threads

T
  • Solved
Copy paste value when selecting from drop-down list
Replies
2
Views
478
tinezi
T
M
  • Question
Copy selected column data and paste into another sheet.
Replies
3
Views
220
motilulla
M
Unlucky
  • Solved
INDEX & MATCH
Replies
4
Views
577
Fluff
Fluff
E
  • Solved
Get a 2D spilled formula to fulfill a distribution table matching the ranges of both dimensions
Replies
3
Views
62
Fluff
Fluff
C
  • Solved
Index Formula with One Condition Issue
Replies
8
Views
408
Fluff
Fluff

Forum statistics

Threads
1,214,918
Messages
6,122,249
Members
449,075
Latest member
staticfluids

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