Previous Topic Next Topic

Excel formula

Closed
Report
Posts
2
Registration date
Thursday August 13, 2009
Status
Member
Last seen
August 15, 2009
 -
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
 -
Hello,

i have one excel sheet,

the first sheet has master data with various inputs like

No,remarks
1,hello
2, hai
3, bye
4, good
5, bad
6, ok

i open another sheet, pasting the new values

No Remarks
1, hello
2, hello
3, good night
4, bad
5, good
6, not ok


All i want is, while pasting these entries in 2nd sheet, my first sheet should be automatically updated the new remarks against each number,

mapping shoud be No(Sheet 1)=No(Sheet 2), if this satisfies the condition then the remarks(shee1) should be replaced with remarks(sheet2)

after applying forula it shoudl look like

No,Remarks
1, hello
2, hello
3, good night
4, bad
5, good
6, not ok

Pls help

4 replies

Answer 1 / 4
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
 12
Use Vlookup() formula in remarks column at first sheet.
assume you have 2 sheets on one workbook.
first sheet is "sheet1"
second sheet is "sheet2"
at first sheet on column "remarks"
=IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,0)),"",VLOOKUP(A2,Sheet2!A:B,2,0))


this may help you.......
Answer 2 / 4
Posts
2
Registration date
Thursday August 13, 2009
Status
Member
Last seen
August 15, 2009
wonderful, thank you so much,

one more, if my second sheet has more than 6 entries, how to update the master sheet with the extra entries

example

No,remarks
1,hello
2, hai
3, bye
4, good
5, bad
6, ok

i open another sheet, pasting the new values

No Remarks
1, hello
2, hello
3, good night
4, bad
5, good
6, not ok
7, excellent
8, fine

then master sheet should be

No,Remarks
1, hello
2, hello
3, good night
4, bad
5, good
6, not ok
7, excellent
8, fine

thanks in advance..
Answer 3 / 4
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
 12
Try this on your VBA

Sub Update1()

Dim A, B As String
Dim i, j As Integer
A = 0
i = 2
Do Until IsNull(A) Or A = ""
A = Sheets("Sheet1").Cells(i, 1)
i = i + 1
Loop
B = 0
j = 2
Do Until IsNull(B) Or B = ""
B = Sheets("Sheet2").Cells(j, 1)
j = j + 1
Loop

If i < j Then
For i = i + 1 To j + 1
Sheets("Sheet1").Cells(i, 1).Value = Sheets("Sheet2").Cells(i, 1).Value
temp = "=IF(ISERROR(VLOOKUP(A" & i & ",Sheet2!A:B,2,0))," & "No data" & ",VLOOKUP(A" & i & ",Sheet2!A:B,2,0)) "
Sheets("Sheet1").Cells(i, 2).Formula = temp
Next i
End If

End Sub
Answer 4 / 4
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
 12
Update.........i miss some code on previous

Sub Update1()

Dim A, B As String
Dim i, j As Integer
A = 0
i = 2
Do Until IsNull(A) Or A = ""
A = Sheets("Sheet1").Cells(i, 1)
i = i + 1
Loop
B = 0
j = 1
Do Until IsNull(B) Or B = ""
B = Sheets("Sheet2").Cells(j, 1)
j = j + 1
Loop
i = i - 2
j = j - 2
If i < j Then
For i = i + 1 To j
Sheets("Sheet1").Cells(i, 1).Value = Sheets("Sheet2").Cells(i, 1).Value
temp = "=IF(ISERROR(VLOOKUP(A" & i & ",Sheet2!A:B,2,0))," & "No data" & ",VLOOKUP(A" & i & ",Sheet2!A:B,2,0)) "
Sheets("Sheet1").Cells(i, 2).Formula = temp
Next i
End If

End Sub
Ask a question