Search : in
By :

Excel formula

Last answer on Aug 15, 2009 11:35:23 am BST aji1311, on Aug 14, 2009 3:53:41 pm BST 
 Report this message to moderators

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

Configuration: Windows 2000 Professional, Microsoft Excel 2003

Best answers for « excel formula » in :
Excel – Formula for cell calculation Show Excel – Formula for cell calculation Issue Solution Note Issue Simple formula of =J3-SUM(L3:X3) BUT if J3 is empty then I want the formula to run as =C3-SUM(L3:X3) What is the proper formula to get the calculation to utilize J3 if...
Excel formula to get the cell with the text Show
Excel Formula Display in Cell (Solved) Show
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL + ; in the chosen cell. To insert current time, press CTRL+...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Introduction to encryption with DES ShowDES, secret-key decryption On 15 May 1973, the NBS (National Bureau of Standards, now called NIST - National Institute of Standards and Technology) published a request in the Federal Register for an encryption algorithm that would meet the...
Ask your question Reply

1

kaiyasit, on Aug 15, 2009 2:45:56 am BST

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.......

   
Reply to kaiyasit

2

aji1311, on Aug 15, 2009 7:49:29 am BST

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..

   
Reply to aji1311

3

kaiyasit, on Aug 15, 2009 10:55:25 am BST

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

   
Reply to kaiyasit

4

 kaiyasit, on Aug 15, 2009 11:35:23 am BST

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

   
Reply to kaiyasit
Ask your question Reply
They need your help