Previous Topic Next Topic

Excel formula

Closed
aji1311 Posts 2 Registration date Thursday August 13, 2009 Status Member Last seen August 15, 2009 - Aug 14, 2009 at 10:53 AM
kaiyasit Posts 30 Registration date Sunday August 9, 2009 Status Member Last seen April 20, 2010 - Aug 15, 2009 at 06:35 AM
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
kaiyasit Posts 30 Registration date Sunday August 9, 2009 Status Member Last seen April 20, 2010 12
Aug 14, 2009 at 09:45 PM
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.......
0
Answer 2 / 4
aji1311 Posts 2 Registration date Thursday August 13, 2009 Status Member Last seen August 15, 2009
Aug 15, 2009 at 02:49 AM
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..
0
Answer 3 / 4
kaiyasit Posts 30 Registration date Sunday August 9, 2009 Status Member Last seen April 20, 2010 12
Aug 15, 2009 at 05:55 AM
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
0
Answer 4 / 4
kaiyasit Posts 30 Registration date Sunday August 9, 2009 Status Member Last seen April 20, 2010 12
Aug 15, 2009 at 06:35 AM
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
0