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
kaiyasit Posts 30 Registration date Sunday August 9, 2009 Status Member Last seen April 20, 2010 - Aug 15, 2009 at 06:35 AM
Related:
- Excel formula
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel mod apk for pc - Download - Spreadsheets
4 responses
kaiyasit
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
12
Aug 14, 2009 at 09:45 PM
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.......
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.......
aji1311
Posts
2
Registration date
Thursday August 13, 2009
Status
Member
Last seen
August 15, 2009
Aug 15, 2009 at 02:49 AM
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..
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..
kaiyasit
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
12
Aug 15, 2009 at 05:55 AM
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
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
kaiyasit
Posts
30
Registration date
Sunday August 9, 2009
Status
Member
Last seen
April 20, 2010
12
Aug 15, 2009 at 06:35 AM
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
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
