Good Luck w/This one: Excel Question VBA/Macro/other

[TKSS]

Hi all, I have a three part question with each inhingent upon each other.

1. Is there a function in excel that isn't global that will call the time and date? I'm using an IF function to drop the date into a column when text is input into the cell next to it. I'd like to be able to have this operate as a 'timestamp' where the time and date are unique to when the person altered the cell next to the IF statement. If one can answer this one...I won't need to do #2 and #3.

2. If there is no solution to #1, then I'll have to go about this a different way :evil: : I have four columns of text in a 8 column spreadsheet that I'd like to display onto another worksheet in the current workbook. So then I'd have say...columns A-D out of A-H on sheet1 displayed on sheet2. I'm not sure how to accomplish this in excel. I know an inner join in Access would probably work...but what about excel? If this is possible, I'll need help with #3 and a clean pair of shorts.

3. So after this (#2) is accomplished, I need to be able to time stamp a change from column C on sheet2. So if there are any changes to column A-C on sheet1...they'll auto update to sheet2...and if there are any changes specifically to column C on either sheet...a timestamp will drop into column E on sheet2. Anyone know if this is possible? if you need more details let me know and I can provide them.

TKS

[tonyd]

Try this.

You cannot do what you want with a spreadsheet function, since it would recalculate each time you make a change on the sheet, resulting in the current date.You will have to create a vb routine to select a single cell by using the Worksheet_Change event. I work both on excel 2000 & 2003.

1. Alt+F11 activates the VB editor
2. In the Project Explorer, right click the sheet object (excel work sheet in the workbook) that you want the date stamp on.
3. In the Object drop down box (in the upper, L-H corner of code window) select Worksheet
4. In the Procedure window (in the upper, R-H corner of code window) select Change
5. Now code for column A changing and putting the DateStamp in column B(modify for your specific situation)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("A:A").Column Then
Cells(.Row, "B").Value = Int(Now)
End If
End With
Next Cell
End Sub

Good Luck!

Hi all, I have a three part question with each inhingent upon each other.

1. Is there a function in excel that isn't global that will call the time and date? I'm using an IF function to drop the date into a column when text is input into the cell next to it. I'd like to be able to have this operate as a 'timestamp' where the time and date are unique to when the person altered the cell next to the IF statement. If one can answer this one...I won't need to do #2 and #3.

2. If there is no solution to #1, then I'll have to go about this a different way :evil: : I have four columns of text in a 8 column spreadsheet that I'd like to display onto another worksheet in the current workbook. So then I'd have say...columns A-D out of A-H on sheet1 displayed on sheet2. I'm not sure how to accomplish this in excel. I know an inner join in Access would probably work...but what about excel? If this is possible, I'll need help with #3 and a clean pair of shorts.

3. So after this (#2) is accomplished, I need to be able to time stamp a change from column C on sheet2. So if there are any changes to column A-C on sheet1...they'll auto update to sheet2...and if there are any changes specifically to column C on either sheet...a timestamp will drop into column E on sheet2. Anyone know if this is possible? if you need more details let me know and I can provide them.

TKS

[tonyd]

Oops, if you are using this from a template be sure to save back as a template!

Try this.

You cannot do what you want with a spreadsheet function, since it would recalculate each time you make a change on the sheet, resulting in the current date.You will have to create a vb routine to select a single cell by using the Worksheet_Change event. I work both on excel 2000 & 2003.

1. Alt+F11 activates the VB editor
2. In the Project Explorer, right click the sheet object (excel work sheet in the workbook) that you want the date stamp on.
3. In the Object drop down box (in the upper, L-H corner of code window) select Worksheet
4. In the Procedure window (in the upper, R-H corner of code window) select Change
5. Now code for column A changing and putting the DateStamp in column B (modify for your specific situation)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("A:A").Column Then
Cells(.Row, "B").Value = Int(Now)
End If
End With
Next Cell
End Sub

Good Luck!

Hi all, I have a three part question with each inhingent upon each other.

1. Is there a function in excel that isn't global that will call the time and date? I'm using an IF function to drop the date into a column when text is input into the cell next to it. I'd like to be able to have this operate as a 'timestamp' where the time and date are unique to when the person altered the cell next to the IF statement. If one can answer this one...I won't need to do #2 and #3.

2. If there is no solution to #1, then I'll have to go about this a different way :evil: : I have four columns of text in a 8 column spreadsheet that I'd like to display onto another worksheet in the current workbook. So then I'd have say...columns A-D out of A-H on sheet1 displayed on sheet2. I'm not sure how to accomplish this in excel. I know an inner join in Access would probably work...but what about excel? If this is possible, I'll need help with #3 and a clean pair of shorts.

3. So after this (#2) is accomplished, I need to be able to time stamp a change from column C on sheet2. So if there are any changes to column A-C on sheet1...they'll auto update to sheet2...and if there are any changes specifically to column C on either sheet...a timestamp will drop into column E on sheet2. Anyone know if this is possible? if you need more details let me know and I can provide them.

TKS