•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS Access and FileMaker Pro section within the Web Development category of DaniWeb, a massive community of 402,904 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,111 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS Access and FileMaker Pro advertiser: Programming Forums
Views: 17545 | Replies: 2
 |
| |
•
•
Join Date: Jan 2004
Location: VA, USA
Posts: 459
Reputation: 
Rep Power: 6
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
•
•
Join Date: Dec 2004
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Re: Good Luck w/This one: Excel Question VBA/Macro/other 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
•
•
Join Date: Dec 2004
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
|
•
•
•
•
•
•
•
•
DaniWeb MS Access and FileMaker Pro Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Hybrid Mode
- Excel Macro Save as with date in file name (Visual Basic 4 / 5 / 6)
- VBA Macro Coding Question (Visual Basic 4 / 5 / 6)
- Visual C++ and Excel (C++)
- 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
