 |  |  |  |  |  |  |  |
Good Luck w/This one: Excel Question VBA/Macro/other
Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
•
•
Join Date: Jan 2004
Posts: 468
Reputation: 
Solved Threads: 17
Posting Pro in Training
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
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
My Home Away from Home: Yet Another Linux Blog
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!
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!
•
•
•
•
Originally Posted by TKS
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
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!
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!
•
•
•
•
Originally Posted by TKS
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
|
Similar Threads
- Excel VBA macro protection (Visual Basic 4 / 5 / 6)
- Homework help that isn't really homework help (Java)
- C# and VBA problem.... (C#)
- Login Screen on boot before user screen? (Windows NT / 2000 / XP)
- Good Luck! (Geeks' Lounge)
Other Threads in the MS Access and FileMaker Pro Forum
- Previous Thread: How to create multiple toggle field codes in Word.
- Next Thread: power surge destroyed filemaker data
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest MS Access and FileMaker Pro Posts
- Today's Posts
- Unanswered Threads