Go back to update my data with my data entry sheet
 

Hi, I created a data entry sheet (sheet1) where different people will fill it
out and the data will be saved in sheet2. I would like to know if is posible
to update old records by using the entry sheet (sheet1). I using Excel 2007,
but would like to have something to work in early versions as well.

Thank you.
AnnaC

Go back to update my data with my data entry sheet
 

The answer is yes. You could have a person enter a name or ID into a cell
(box, userform,...) and then check if the name (or ID) is already located in
sheet 2. If it is you can copy the data in sheet 2 back into sheet 1 before
letting the person change sheet 1. Then when done put the data back into
sheet 2. If the person is not already in sheet 2 then clear sheet one and
have the person add all the information from scratch.

"AnnaC" wrote:

Hi, I created a data entry sheet (sheet1) where different people will fill it
out and the data will be saved in sheet2. I would like to know if is posible
to update old records by using the entry sheet (sheet1). I using Excel 2007,
but would like to have something to work in early versions as well.

Thank you.
AnnaC

Go back to update my data with my data entry sheet
 

Thank you, I am not good at crating macros could I get a macro sample. Thank
you again.

"joel" wrote:

The answer is yes. You could have a person enter a name or ID into a cell
(box, userform,...) and then check if the name (or ID) is already located in
sheet 2. If it is you can copy the data in sheet 2 back into sheet 1 before
letting the person change sheet 1. Then when done put the data back into
sheet 2. If the person is not already in sheet 2 then clear sheet one and
have the person add all the information from scratch.

"AnnaC" wrote:

Hi, I created a data entry sheet (sheet1) where different people will fill it
out and the data will be saved in sheet2. I would like to know if is posible
to update old records by using the entry sheet (sheet1). I using Excel 2007,
but would like to have something to work in early versions as well.

Thank you.
AnnaC

Go back to update my data with my data entry sheet
 

Sub GetData()
Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'clear Entry sheet
EntrySht.Cells.ClearContents
'Put New ID in New Row
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Range("A" & DataRow) = ID
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row
'example of moving data from data sheet
'to entry sheet
EntrySht.Range("A2") = .Range("B" & c.Row)
'move all old data from data sheet to
'entry sheet here
End With
End If

'Now let user edit the Entry sheet
'then when user is finished
'have them press a submit button
'and run another macro to move data
'from entry sheet to data sheet

'
End Sub
Sub submit()

Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Else
DataRow = c.Row
End If

'Enter your code here to move data from entry sheet
'to data sheet
DataSht.Range("B" & DataRow) = EntrySht.Range("D4")
End Sub


"AnnaC" wrote:

Thank you, I am not good at crating macros could I get a macro sample. Thank
you again.

"joel" wrote:

The answer is yes. You could have a person enter a name or ID into a cell
(box, userform,...) and then check if the name (or ID) is already located in
sheet 2. If it is you can copy the data in sheet 2 back into sheet 1 before
letting the person change sheet 1. Then when done put the data back into
sheet 2. If the person is not already in sheet 2 then clear sheet one and
have the person add all the information from scratch.

"AnnaC" wrote:

Hi, I created a data entry sheet (sheet1) where different people will fill it
out and the data will be saved in sheet2. I would like to know if is posible
to update old records by using the entry sheet (sheet1). I using Excel 2007,
but would like to have something to work in early versions as well.

Thank you.
AnnaC

Go back to update my data with my data entry sheet
 

Thank you for your help!

"joel" wrote:

Sub GetData()
Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'clear Entry sheet
EntrySht.Cells.ClearContents
'Put New ID in New Row
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Range("A" & DataRow) = ID
Else
'move old data from data sheet to entry sheet
With DataSht
DataRow = c.Row
'example of moving data from data sheet
'to entry sheet
EntrySht.Range("A2") = .Range("B" & c.Row)
'move all old data from data sheet to
'entry sheet here
End With
End If

'Now let user edit the Entry sheet
'then when user is finished
'have them press a submit button
'and run another macro to move data
'from entry sheet to data sheet

'
End Sub
Sub submit()

Set EntrySht = Sheets("Sheet1")
Set DataSht = Sheets("Sheet2")

ID = EntrySht.Range("A1")

'See if ID already exists
Set c = DataSht.Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
LastRow = DataSht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
DataRow = NewRow
Else
DataRow = c.Row
End If

'Enter your code here to move data from entry sheet
'to data sheet
DataSht.Range("B" & DataRow) = EntrySht.Range("D4")
End Sub


"AnnaC" wrote:

Thank you, I am not good at crating macros could I get a macro sample. Thank
you again.

"joel" wrote:

The answer is yes. You could have a person enter a name or ID into a cell
(box, userform,...) and then check if the name (or ID) is already located in
sheet 2. If it is you can copy the data in sheet 2 back into sheet 1 before
letting the person change sheet 1. Then when done put the data back into
sheet 2. If the person is not already in sheet 2 then clear sheet one and
have the person add all the information from scratch.

"AnnaC" wrote:

Hi, I created a data entry sheet (sheet1) where different people will fill it
out and the data will be saved in sheet2. I would like to know if is posible
to update old records by using the entry sheet (sheet1). I using Excel 2007,
but would like to have something to work in early versions as well.

Thank you.
AnnaC