update sheet2 column automatically based on sheet1 values using formula or vba

learning_grexcel

Active Member
Joined
Jan 29, 2011
Messages
319
Hi,

I have two sheets linked to each other as shown below :

Sheet 1 :


Cell Formulas
RangeFormula
B1XYZ Company
B2Price
B335
B423
B512
B618
B719
B8234
B925
B1026
B1127
B1228
B1329
B1430
B1531
B1612
B1724
B1818
B1919
B2025
B2195
B22213
B23143
B2413
B255
B2624
B2736
F1ABC Company
F2Price
F335
F423
F512
F618
F719
F8234
F925
F1026
F1127
F1228
F1329
F1430
F1531
F1612
F1724
F1818
F1919
F2025
F2195
F22213
F23143
F2413
F255
F2624
F2736
I1LMN company
I2Sr. No.
I312345
I412346
I512347
I612389
I712349
I812350
I912351
I1012352
I1112353
I1212354
I1312355
I1412356
I1512357
I1612358
I1712359
I1812360
I1912361
I2012362
I2112381
I2212364
I2312365
I2412366
I2512367
I2612368
I2712390
A2Sr. No.
A312345
A412346
A512347
A612348
A712349
A812350
A912371
A1012352
A1112353
A1212354
A1312355
A1412382
A1512357
A1612358
A1712359
A1812360
A1912395
A2012362
A2112363
A2212364
A2312387
A2412366
A2512367
A2612368
A2712369
E2Sr. No.
E312345
E412346
E512347
E612391
E712349
E812350
E912351
E1012352
E1112353
E1212398
E1312355
E1412356
E1512357
E1612358
E1712359
E1812360
E1912361
E2012362
E2112363
E2212364
E2312365
E2412366
E2512367
E2612368
E2712369
J2Price
J335
J423
J512
J618
J719
J8234
J925
J1026
J1127
J1228
J1329
J1430
J1531
J1612
J1724
J1818
J1919
J2025
J2195
J22213
J23143
J2413
J255
J2624
J2736


sheet2 :

Excel 2012
ABCDE
1Sr. No.XYZ PriceABC PriceLMN PriceTotal Price
212341#N/A#N/A#N/A0
312342#N/A#N/A#N/A0
412343#N/A#N/A#N/A0
512344#N/A#N/A#N/A0
612345353535105
71234623232369
81234712121236
91234818#N/A#N/A18
101234919191957
1112372#N/A#N/A#N/A0
1212373#N/A#N/A#N/A0
1312374#N/A#N/A#N/A0
1412375#N/A#N/A#N/A0
1512376#N/A#N/A#N/A0
1612377#N/A#N/A#N/A0
1712378#N/A#N/A#N/A0
1812379#N/A#N/A#N/A0
1912380#N/A#N/A#N/A0
2012381#N/A#N/A9595
211238230#N/A#N/A30
Sheet2
Cell Formulas
RangeFormula
B2=VLOOKUP(A2,Sheet1!$A$2:$B$27,2,0)
B3=VLOOKUP(A3,Sheet1!$A$2:$B$27,2,0)
B4=VLOOKUP(A4,Sheet1!$A$2:$B$27,2,0)
B5=VLOOKUP(A5,Sheet1!$A$2:$B$27,2,0)
B6=VLOOKUP(A6,Sheet1!$A$2:$B$27,2,0)
B7=VLOOKUP(A7,Sheet1!$A$2:$B$27,2,0)
B8=VLOOKUP(A8,Sheet1!$A$2:$B$27,2,0)
B9=VLOOKUP(A9,Sheet1!$A$2:$B$27,2,0)
B10=VLOOKUP(A10,Sheet1!$A$2:$B$27,2,0)
B11=VLOOKUP(A11,Sheet1!$A$2:$B$27,2,0)
B12=VLOOKUP(A12,Sheet1!$A$2:$B$27,2,0)
B13=VLOOKUP(A13,Sheet1!$A$2:$B$27,2,0)
B14=VLOOKUP(A14,Sheet1!$A$2:$B$27,2,0)
B15=VLOOKUP(A15,Sheet1!$A$2:$B$27,2,0)
B16=VLOOKUP(A16,Sheet1!$A$2:$B$27,2,0)
B17=VLOOKUP(A17,Sheet1!$A$2:$B$27,2,0)
B18=VLOOKUP(A18,Sheet1!$A$2:$B$27,2,0)
B19=VLOOKUP(A19,Sheet1!$A$2:$B$27,2,0)
B20=VLOOKUP(A20,Sheet1!$A$2:$B$27,2,0)
B21=VLOOKUP(A21,Sheet1!$A$2:$B$27,2,0)
C2=VLOOKUP(A2,Sheet1!E$2:$F$27,2,0)
C3=VLOOKUP(A3,Sheet1!E$2:$F$27,2,0)
C4=VLOOKUP(A4,Sheet1!E$2:$F$27,2,0)
C5=VLOOKUP(A5,Sheet1!E$2:$F$27,2,0)
C6=VLOOKUP(A6,Sheet1!E$2:$F$27,2,0)
C7=VLOOKUP(A7,Sheet1!E$2:$F$27,2,0)
C8=VLOOKUP(A8,Sheet1!E$2:$F$27,2,0)
C9=VLOOKUP(A9,Sheet1!E$2:$F$27,2,0)
C10=VLOOKUP(A10,Sheet1!E$2:$F$27,2,0)
C11=VLOOKUP(A11,Sheet1!E$2:$F$27,2,0)
C12=VLOOKUP(A12,Sheet1!E$2:$F$27,2,0)
C13=VLOOKUP(A13,Sheet1!E$2:$F$27,2,0)
C14=VLOOKUP(A14,Sheet1!E$2:$F$27,2,0)
C15=VLOOKUP(A15,Sheet1!E$2:$F$27,2,0)
C16=VLOOKUP(A16,Sheet1!E$2:$F$27,2,0)
C17=VLOOKUP(A17,Sheet1!E$2:$F$27,2,0)
C18=VLOOKUP(A18,Sheet1!E$2:$F$27,2,0)
C19=VLOOKUP(A19,Sheet1!E$2:$F$27,2,0)
C20=VLOOKUP(A20,Sheet1!E$2:$F$27,2,0)
C21=VLOOKUP(A21,Sheet1!E$2:$F$27,2,0)
D2=VLOOKUP(A2,Sheet1!$I$2:$J$27,2,0)
D3=VLOOKUP(A3,Sheet1!$I$2:$J$27,2,0)
D4=VLOOKUP(A4,Sheet1!$I$2:$J$27,2,0)
D5=VLOOKUP(A5,Sheet1!$I$2:$J$27,2,0)
D6=VLOOKUP(A6,Sheet1!$I$2:$J$27,2,0)
D7=VLOOKUP(A7,Sheet1!$I$2:$J$27,2,0)
D8=VLOOKUP(A8,Sheet1!$I$2:$J$27,2,0)
D9=VLOOKUP(A9,Sheet1!$I$2:$J$27,2,0)
D10=VLOOKUP(A10,Sheet1!$I$2:$J$27,2,0)
D11=VLOOKUP(A11,Sheet1!$I$2:$J$27,2,0)
D12=VLOOKUP(A12,Sheet1!$I$2:$J$27,2,0)
D13=VLOOKUP(A13,Sheet1!$I$2:$J$27,2,0)
D14=VLOOKUP(A14,Sheet1!$I$2:$J$27,2,0)
D15=VLOOKUP(A15,Sheet1!$I$2:$J$27,2,0)
D16=VLOOKUP(A16,Sheet1!$I$2:$J$27,2,0)
D17=VLOOKUP(A17,Sheet1!$I$2:$J$27,2,0)
D18=VLOOKUP(A18,Sheet1!$I$2:$J$27,2,0)
D19=VLOOKUP(A19,Sheet1!$I$2:$J$27,2,0)
D20=VLOOKUP(A20,Sheet1!$I$2:$J$27,2,0)
D21=VLOOKUP(A21,Sheet1!$I$2:$J$27,2,0)
E2=SUM(IFERROR(B2,0),IFERROR(C2,0),IFERROR(D2,0))
E3=SUM(IFERROR(B3,0),IFERROR(C3,0),IFERROR(D3,0))
E4=SUM(IFERROR(B4,0),IFERROR(C4,0),IFERROR(D4,0))
E5=SUM(IFERROR(B5,0),IFERROR(C5,0),IFERROR(D5,0))
E6=SUM(IFERROR(B6,0),IFERROR(C6,0),IFERROR(D6,0))
E7=SUM(IFERROR(B7,0),IFERROR(C7,0),IFERROR(D7,0))
E8=SUM(IFERROR(B8,0),IFERROR(C8,0),IFERROR(D8,0))
E9=SUM(IFERROR(B9,0),IFERROR(C9,0),IFERROR(D9,0))
E10=SUM(IFERROR(B10,0),IFERROR(C10,0),IFERROR(D10,0))
E11=SUM(IFERROR(B11,0),IFERROR(C11,0),IFERROR(D11,0))
E12=SUM(IFERROR(B12,0),IFERROR(C12,0),IFERROR(D12,0))
E13=SUM(IFERROR(B13,0),IFERROR(C13,0),IFERROR(D13,0))
E14=SUM(IFERROR(B14,0),IFERROR(C14,0),IFERROR(D14,0))
E15=SUM(IFERROR(B15,0),IFERROR(C15,0),IFERROR(D15,0))
E16=SUM(IFERROR(B16,0),IFERROR(C16,0),IFERROR(D16,0))
E17=SUM(IFERROR(B17,0),IFERROR(C17,0),IFERROR(D17,0))
E18=SUM(IFERROR(B18,0),IFERROR(C18,0),IFERROR(D18,0))
E19=SUM(IFERROR(B19,0),IFERROR(C19,0),IFERROR(D19,0))
E20=SUM(IFERROR(B20,0),IFERROR(C20,0),IFERROR(D20,0))
E21=SUM(IFERROR(B21,0),IFERROR(C21,0),IFERROR(D21,0))


sheet1 data is replaced(copied and pasted from other sources on both columns for all three companies, e.g. A3:B27 is replaced with new values, E3:F27 is replaced with new values and similarly i3:j27) every day. However, sheet2 Sr.Nos. are fixed list, only price values are pulled from sheet1.


These are formulas within sheet2 :
column B = vlookup(A2,sheet1$A$2:$B$27,2,0)
column C = VLOOKUP(A2,Sheet1!E$2:$F$27,2,0)
column D = VLOOKUP(A2,Sheet1!$I$2:$J$27,2,0)
column E = =SUM(IFERROR(B2,0),IFERROR(C2,0),IFERROR(D2,0))


Sr. No. in column A, column E, column I of sheet1 can change any day. However, sheet2 Sr.nos are complete sr.nos list as of now put manually by me. I want to make sure no Sr. No. of sheet1 is missing in sheet2. In case if it is missing in Sheet2, it should automatically pull from sheet1. Is it possible?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,223,712
Messages
6,174,031
Members
452,542
Latest member
Bricklin

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