learning_grexcel
Active Member
- Joined
- Jan 29, 2011
- Messages
- 319
Hi,
I have two sheets linked to each other as shown below :
Sheet 1 :
sheet2 :
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?
I have two sheets linked to each other as shown below :
Sheet 1 :
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | XYZ Company | |
B2 | Price | |
B3 | 35 | |
B4 | 23 | |
B5 | 12 | |
B6 | 18 | |
B7 | 19 | |
B8 | 234 | |
B9 | 25 | |
B10 | 26 | |
B11 | 27 | |
B12 | 28 | |
B13 | 29 | |
B14 | 30 | |
B15 | 31 | |
B16 | 12 | |
B17 | 24 | |
B18 | 18 | |
B19 | 19 | |
B20 | 25 | |
B21 | 95 | |
B22 | 213 | |
B23 | 143 | |
B24 | 13 | |
B25 | 5 | |
B26 | 24 | |
B27 | 36 | |
F1 | ABC Company | |
F2 | Price | |
F3 | 35 | |
F4 | 23 | |
F5 | 12 | |
F6 | 18 | |
F7 | 19 | |
F8 | 234 | |
F9 | 25 | |
F10 | 26 | |
F11 | 27 | |
F12 | 28 | |
F13 | 29 | |
F14 | 30 | |
F15 | 31 | |
F16 | 12 | |
F17 | 24 | |
F18 | 18 | |
F19 | 19 | |
F20 | 25 | |
F21 | 95 | |
F22 | 213 | |
F23 | 143 | |
F24 | 13 | |
F25 | 5 | |
F26 | 24 | |
F27 | 36 | |
I1 | LMN company | |
I2 | Sr. No. | |
I3 | 12345 | |
I4 | 12346 | |
I5 | 12347 | |
I6 | 12389 | |
I7 | 12349 | |
I8 | 12350 | |
I9 | 12351 | |
I10 | 12352 | |
I11 | 12353 | |
I12 | 12354 | |
I13 | 12355 | |
I14 | 12356 | |
I15 | 12357 | |
I16 | 12358 | |
I17 | 12359 | |
I18 | 12360 | |
I19 | 12361 | |
I20 | 12362 | |
I21 | 12381 | |
I22 | 12364 | |
I23 | 12365 | |
I24 | 12366 | |
I25 | 12367 | |
I26 | 12368 | |
I27 | 12390 | |
A2 | Sr. No. | |
A3 | 12345 | |
A4 | 12346 | |
A5 | 12347 | |
A6 | 12348 | |
A7 | 12349 | |
A8 | 12350 | |
A9 | 12371 | |
A10 | 12352 | |
A11 | 12353 | |
A12 | 12354 | |
A13 | 12355 | |
A14 | 12382 | |
A15 | 12357 | |
A16 | 12358 | |
A17 | 12359 | |
A18 | 12360 | |
A19 | 12395 | |
A20 | 12362 | |
A21 | 12363 | |
A22 | 12364 | |
A23 | 12387 | |
A24 | 12366 | |
A25 | 12367 | |
A26 | 12368 | |
A27 | 12369 | |
E2 | Sr. No. | |
E3 | 12345 | |
E4 | 12346 | |
E5 | 12347 | |
E6 | 12391 | |
E7 | 12349 | |
E8 | 12350 | |
E9 | 12351 | |
E10 | 12352 | |
E11 | 12353 | |
E12 | 12398 | |
E13 | 12355 | |
E14 | 12356 | |
E15 | 12357 | |
E16 | 12358 | |
E17 | 12359 | |
E18 | 12360 | |
E19 | 12361 | |
E20 | 12362 | |
E21 | 12363 | |
E22 | 12364 | |
E23 | 12365 | |
E24 | 12366 | |
E25 | 12367 | |
E26 | 12368 | |
E27 | 12369 | |
J2 | Price | |
J3 | 35 | |
J4 | 23 | |
J5 | 12 | |
J6 | 18 | |
J7 | 19 | |
J8 | 234 | |
J9 | 25 | |
J10 | 26 | |
J11 | 27 | |
J12 | 28 | |
J13 | 29 | |
J14 | 30 | |
J15 | 31 | |
J16 | 12 | |
J17 | 24 | |
J18 | 18 | |
J19 | 19 | |
J20 | 25 | |
J21 | 95 | |
J22 | 213 | |
J23 | 143 | |
J24 | 13 | |
J25 | 5 | |
J26 | 24 | |
J27 | 36 |
sheet2 :
Excel 2012 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Sr. No. | XYZ Price | ABC Price | LMN Price | Total Price | ||
2 | 12341 | #N/A | #N/A | #N/A | 0 | ||
3 | 12342 | #N/A | #N/A | #N/A | 0 | ||
4 | 12343 | #N/A | #N/A | #N/A | 0 | ||
5 | 12344 | #N/A | #N/A | #N/A | 0 | ||
6 | 12345 | 35 | 35 | 35 | 105 | ||
7 | 12346 | 23 | 23 | 23 | 69 | ||
8 | 12347 | 12 | 12 | 12 | 36 | ||
9 | 12348 | 18 | #N/A | #N/A | 18 | ||
10 | 12349 | 19 | 19 | 19 | 57 | ||
11 | 12372 | #N/A | #N/A | #N/A | 0 | ||
12 | 12373 | #N/A | #N/A | #N/A | 0 | ||
13 | 12374 | #N/A | #N/A | #N/A | 0 | ||
14 | 12375 | #N/A | #N/A | #N/A | 0 | ||
15 | 12376 | #N/A | #N/A | #N/A | 0 | ||
16 | 12377 | #N/A | #N/A | #N/A | 0 | ||
17 | 12378 | #N/A | #N/A | #N/A | 0 | ||
18 | 12379 | #N/A | #N/A | #N/A | 0 | ||
19 | 12380 | #N/A | #N/A | #N/A | 0 | ||
20 | 12381 | #N/A | #N/A | 95 | 95 | ||
21 | 12382 | 30 | #N/A | #N/A | 30 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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?