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

L

learning_grexcel

Active Member
Joined
Jan 29, 2011
Messages
319
Hi,

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

Sheet 1 :


Excel 2012
ABCDEFGHIJ
1XYZ CompanyABC CompanyLMN company
2Sr. No.PriceSr. No.PriceSr. No.Price
3123453512345351234535
4123462312346231234623
5123471212347121234712
6123481812391181238918
7123491912349191234919
8123502341235023412350234
9123712512351251235125
10123522612352261235226
11123532712353271235327
12123542812398281235428
13123552912355291235529
14123823012356301235630
15123573112357311235731
16123581212358121235812
17123592412359241235924
18123601812360181236018
19123951912361191236119
20123622512362251236225
21123639512363951238195
22123642131236421312364213
23123871431236514312365143
24123661312366131236613
25123675123675123675
26123682412368241236824
27123693612369361239036
Sheet1
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 is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You must log in or register to reply here.

Similar threads

gtd526
  • Question
search and answer only ODD rows
Replies
6
Views
143
gtd526
gtd526
S
  • Question
A formula to fetch the last value in a column using several criteria.
Replies
1
Views
110
Peter_SSs
Peter_SSs
VinceF
  • Question
VBA To Copy & Paste Value
Replies
0
Views
155
VinceF
VinceF
N
  • Question
Highest Value(s) displaying text
Replies
8
Views
322
Fluff
Fluff
L
  • Solved
Formula in the helper column. need duplicate to be count as One
Replies
7
Views
166
KRice
K

Forum statistics

Threads
1,215,034
Messages
6,122,782
Members
449,095
Latest member
m_smith_solihull

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