Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
I have a dashboard type of display on worksheet #1 (Dashboard) where an
individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
You can not do this with formulas...
You need to use a macro (using VBA code) Looks like the value in B3 is written many times during a day... Do you want to keep updating this in Col B in 'Draw' against TODAY's date? "SPISO" wrote: I have a dashboard type of display on worksheet #1 (Dashboard) where an individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
That is exactly what I am looking to do. In essence, the last update of the
day should be the value on Today's row in Col B in 'Draw'. Is that even possible? Travis "Sheeloo" wrote: You can not do this with formulas... You need to use a macro (using VBA code) Looks like the value in B3 is written many times during a day... Do you want to keep updating this in Col B in 'Draw' against TODAY's date? "SPISO" wrote: I have a dashboard type of display on worksheet #1 (Dashboard) where an individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
Right-click on the Dashboard sheet tab at the bottom... and choose 'Code'
Paste the macro below (from start to end lines) in the VB Editor window on the right when it opens up 'start macro Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.ScreenUpdating = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).FormulaR1C1 = Sheets("Dashboard").Cells(3, 2) Sheets("Dashboard").Select Application.ScreenUpdating = True End If End Sub 'end macro close the VB Editor Now whatever you type in B3 will be copied to the draw sheet agains today's date. "SPISO" wrote: That is exactly what I am looking to do. In essence, the last update of the day should be the value on Today's row in Col B in 'Draw'. Is that even possible? Travis "Sheeloo" wrote: You can not do this with formulas... You need to use a macro (using VBA code) Looks like the value in B3 is written many times during a day... Do you want to keep updating this in Col B in 'Draw' against TODAY's date? "SPISO" wrote: I have a dashboard type of display on worksheet #1 (Dashboard) where an individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
Perfect. Thanks so much for your help. If I wanted to do that exact same
thing on the same worksheet (Dashboard) but in a different cell (C37), and copy C37 to a 'Notes' worksheet against date, what would I have to change? This is so cool. WOW! Thanks again. "Sheeloo" wrote: Right-click on the Dashboard sheet tab at the bottom... and choose 'Code' Paste the macro below (from start to end lines) in the VB Editor window on the right when it opens up 'start macro Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.ScreenUpdating = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).FormulaR1C1 = Sheets("Dashboard").Cells(3, 2) Sheets("Dashboard").Select Application.ScreenUpdating = True End If End Sub 'end macro close the VB Editor Now whatever you type in B3 will be copied to the draw sheet agains today's date. "SPISO" wrote: That is exactly what I am looking to do. In essence, the last update of the day should be the value on Today's row in Col B in 'Draw'. Is that even possible? Travis "Sheeloo" wrote: You can not do this with formulas... You need to use a macro (using VBA code) Looks like the value in B3 is written many times during a day... Do you want to keep updating this in Col B in 'Draw' against TODAY's date? "SPISO" wrote: I have a dashboard type of display on worksheet #1 (Dashboard) where an individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
One thing I forgot to mentions on the C37 cell was that it would be text
rather than a value. Does that change anything in your code? "Sheeloo" wrote: Right-click on the Dashboard sheet tab at the bottom... and choose 'Code' Paste the macro below (from start to end lines) in the VB Editor window on the right when it opens up 'start macro Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.ScreenUpdating = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).FormulaR1C1 = Sheets("Dashboard").Cells(3, 2) Sheets("Dashboard").Select Application.ScreenUpdating = True End If End Sub 'end macro close the VB Editor Now whatever you type in B3 will be copied to the draw sheet agains today's date. "SPISO" wrote: That is exactly what I am looking to do. In essence, the last update of the day should be the value on Today's row in Col B in 'Draw'. Is that even possible? Travis "Sheeloo" wrote: You can not do this with formulas... You need to use a macro (using VBA code) Looks like the value in B3 is written many times during a day... Do you want to keep updating this in Col B in 'Draw' against TODAY's date? "SPISO" wrote: I have a dashboard type of display on worksheet #1 (Dashboard) where an individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
No, it does not.
If you want both to work then copy all the lines from IF to END IF (including both), copy just after the END IF line and make the required changes shown below; 'start macro Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then -- change $B$3 to $C$37 Application.ScreenUpdating = False Sheets("Draw").Select -- Change Draw to Notes Sheets("Draw").Columns("A:A").Select -- Change Draw to Notes Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).FormulaR1C1 = Sheets("Dashboard").Cells(3, 2) -- change Cells(3,2) to Cells(37,3) Sheets("Dashboard").Select Application.ScreenUpdating = True End If End Sub 'end macro "SPISO" wrote: One thing I forgot to mentions on the C37 cell was that it would be text rather than a value. Does that change anything in your code? "Sheeloo" wrote: Right-click on the Dashboard sheet tab at the bottom... and choose 'Code' Paste the macro below (from start to end lines) in the VB Editor window on the right when it opens up 'start macro Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.ScreenUpdating = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).FormulaR1C1 = Sheets("Dashboard").Cells(3, 2) Sheets("Dashboard").Select Application.ScreenUpdating = True End If End Sub 'end macro close the VB Editor Now whatever you type in B3 will be copied to the draw sheet agains today's date. "SPISO" wrote: That is exactly what I am looking to do. In essence, the last update of the day should be the value on Today's row in Col B in 'Draw'. Is that even possible? Travis "Sheeloo" wrote: You can not do this with formulas... You need to use a macro (using VBA code) Looks like the value in B3 is written many times during a day... Do you want to keep updating this in Col B in 'Draw' against TODAY's date? "SPISO" wrote: I have a dashboard type of display on worksheet #1 (Dashboard) where an individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
Thanks So Much. It worked. What a time saver.
"Sheeloo" wrote: No, it does not. If you want both to work then copy all the lines from IF to END IF (including both), copy just after the END IF line and make the required changes shown below; 'start macro Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then -- change $B$3 to $C$37 Application.ScreenUpdating = False Sheets("Draw").Select -- Change Draw to Notes Sheets("Draw").Columns("A:A").Select -- Change Draw to Notes Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).FormulaR1C1 = Sheets("Dashboard").Cells(3, 2) -- change Cells(3,2) to Cells(37,3) Sheets("Dashboard").Select Application.ScreenUpdating = True End If End Sub 'end macro "SPISO" wrote: One thing I forgot to mentions on the C37 cell was that it would be text rather than a value. Does that change anything in your code? "Sheeloo" wrote: Right-click on the Dashboard sheet tab at the bottom... and choose 'Code' Paste the macro below (from start to end lines) in the VB Editor window on the right when it opens up 'start macro Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Application.ScreenUpdating = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 1).FormulaR1C1 = Sheets("Dashboard").Cells(3, 2) Sheets("Dashboard").Select Application.ScreenUpdating = True End If End Sub 'end macro close the VB Editor Now whatever you type in B3 will be copied to the draw sheet agains today's date. "SPISO" wrote: That is exactly what I am looking to do. In essence, the last update of the day should be the value on Today's row in Col B in 'Draw'. Is that even possible? Travis "Sheeloo" wrote: You can not do this with formulas... You need to use a macro (using VBA code) Looks like the value in B3 is written many times during a day... Do you want to keep updating this in Col B in 'Draw' against TODAY's date? "SPISO" wrote: I have a dashboard type of display on worksheet #1 (Dashboard) where an individual updates a cell B3 through out the day, everyday, with a new value. On another worksheet (Draw) in column A, I have each date listed from 05/01/09 on down. I would like the updated information in B3 on worksheet 'Dashboard' to locate Today's date on worksheet 'Draw' and insert the updated value next to it in Column B. I tried to use a basic If statement such as: =IF(A2=TODAY(),Dashboard!$B$3,"") in column B on 'Draw' but do not know how to keep it from removing yesterday's value. Also, after midnight, I would like B3 on 'Dashboard' to return to 0 for a new day. Any suggestions? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
You are most welcome.
That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
One more question. Is there a way to revise B3 in Dashboard to change to 0
the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
You have got the right idea...
However make B3 empty. If you change it to 0. 0 will be populated against the date... You can write the code in the worksheet_activate event (signature below) associated with the sheet containing B3 Private Sub Worksheet_Activate() 'Put your code here 'Let me know if you want me to do this 'put an IF around the line where earlier code assigns a value 'use that to change B3 to "" End Sub "SPISO" wrote: One more question. Is there a way to revise B3 in Dashboard to change to 0 the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
I understand why I should not put a zero in B3. That makes since. I am
going to have to plead ignorant on what you were talking about with the code in worksheet_activate event comment though. I am going to need help with that. You have been such a life saver. "Sheeloo" wrote: You have got the right idea... However make B3 empty. If you change it to 0. 0 will be populated against the date... You can write the code in the worksheet_activate event (signature below) associated with the sheet containing B3 Private Sub Worksheet_Activate() 'Put your code here 'Let me know if you want me to do this 'put an IF around the line where earlier code assigns a value 'use that to change B3 to "" End Sub "SPISO" wrote: One more question. Is there a way to revise B3 in Dashboard to change to 0 the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
Right-click on the Dashboard tab, choose View Code and paste the following
either before or after the earlier macro Private Sub Worksheet_Activate() Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then Sheets("Dashboard").Cells(3, 2) = "" End If Sheets("Dashboard").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub 'Note: Application.EnableEvents has to be turnedoff (as done in the code) so that same event is not fired again and again as the code reads the other sheet "SPISO" wrote: I understand why I should not put a zero in B3. That makes since. I am going to have to plead ignorant on what you were talking about with the code in worksheet_activate event comment though. I am going to need help with that. You have been such a life saver. "Sheeloo" wrote: You have got the right idea... However make B3 empty. If you change it to 0. 0 will be populated against the date... You can write the code in the worksheet_activate event (signature below) associated with the sheet containing B3 Private Sub Worksheet_Activate() 'Put your code here 'Let me know if you want me to do this 'put an IF around the line where earlier code assigns a value 'use that to change B3 to "" End Sub "SPISO" wrote: One more question. Is there a way to revise B3 in Dashboard to change to 0 the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
It worked but I was not shocked that it worked after all your other helps
worked as well. Let's say I wanted to use the same code but do it for yesterday and two days ago. I would still want to associate the value next to the dates in "Draw". Could one change your code from =Date to something like =Date-1 and =Date-2 or something like that? Or would it be better to have yesterday's date in a cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your code to associate it with the value in worksheet 'Draw' where the value from 'Draw' can be copied to B4 on the 'Dashboard'? "Sheeloo" wrote: Right-click on the Dashboard tab, choose View Code and paste the following either before or after the earlier macro Private Sub Worksheet_Activate() Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then Sheets("Dashboard").Cells(3, 2) = "" End If Sheets("Dashboard").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub 'Note: Application.EnableEvents has to be turnedoff (as done in the code) so that same event is not fired again and again as the code reads the other sheet "SPISO" wrote: I understand why I should not put a zero in B3. That makes since. I am going to have to plead ignorant on what you were talking about with the code in worksheet_activate event comment though. I am going to need help with that. You have been such a life saver. "Sheeloo" wrote: You have got the right idea... However make B3 empty. If you change it to 0. 0 will be populated against the date... You can write the code in the worksheet_activate event (signature below) associated with the sheet containing B3 Private Sub Worksheet_Activate() 'Put your code here 'Let me know if you want me to do this 'put an IF around the line where earlier code assigns a value 'use that to change B3 to "" End Sub "SPISO" wrote: One more question. Is there a way to revise B3 in Dashboard to change to 0 the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
You can use Date - 1 or Date - 2... Excel treats a date as number of days
from 1/1/1900 so all mathematical operations are possible. You have a programmer's mind... :-) "SPISO" wrote: It worked but I was not shocked that it worked after all your other helps worked as well. Let's say I wanted to use the same code but do it for yesterday and two days ago. I would still want to associate the value next to the dates in "Draw". Could one change your code from =Date to something like =Date-1 and =Date-2 or something like that? Or would it be better to have yesterday's date in a cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your code to associate it with the value in worksheet 'Draw' where the value from 'Draw' can be copied to B4 on the 'Dashboard'? "Sheeloo" wrote: Right-click on the Dashboard tab, choose View Code and paste the following either before or after the earlier macro Private Sub Worksheet_Activate() Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then Sheets("Dashboard").Cells(3, 2) = "" End If Sheets("Dashboard").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub 'Note: Application.EnableEvents has to be turnedoff (as done in the code) so that same event is not fired again and again as the code reads the other sheet "SPISO" wrote: I understand why I should not put a zero in B3. That makes since. I am going to have to plead ignorant on what you were talking about with the code in worksheet_activate event comment though. I am going to need help with that. You have been such a life saver. "Sheeloo" wrote: You have got the right idea... However make B3 empty. If you change it to 0. 0 will be populated against the date... You can write the code in the worksheet_activate event (signature below) associated with the sheet containing B3 Private Sub Worksheet_Activate() 'Put your code here 'Let me know if you want me to do this 'put an IF around the line where earlier code assigns a value 'use that to change B3 to "" End Sub "SPISO" wrote: One more question. Is there a way to revise B3 in Dashboard to change to 0 the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
Thanks again for all your help. I could not have done this without you.
"Sheeloo" wrote: You can use Date - 1 or Date - 2... Excel treats a date as number of days from 1/1/1900 so all mathematical operations are possible. You have a programmer's mind... :-) "SPISO" wrote: It worked but I was not shocked that it worked after all your other helps worked as well. Let's say I wanted to use the same code but do it for yesterday and two days ago. I would still want to associate the value next to the dates in "Draw". Could one change your code from =Date to something like =Date-1 and =Date-2 or something like that? Or would it be better to have yesterday's date in a cell on the dashboard such as =Today()-1 in A4 and refer to that cell in your code to associate it with the value in worksheet 'Draw' where the value from 'Draw' can be copied to B4 on the 'Dashboard'? "Sheeloo" wrote: Right-click on the Dashboard tab, choose View Code and paste the following either before or after the earlier macro Private Sub Worksheet_Activate() Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then Sheets("Dashboard").Cells(3, 2) = "" End If Sheets("Dashboard").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub 'Note: Application.EnableEvents has to be turnedoff (as done in the code) so that same event is not fired again and again as the code reads the other sheet "SPISO" wrote: I understand why I should not put a zero in B3. That makes since. I am going to have to plead ignorant on what you were talking about with the code in worksheet_activate event comment though. I am going to need help with that. You have been such a life saver. "Sheeloo" wrote: You have got the right idea... However make B3 empty. If you change it to 0. 0 will be populated against the date... You can write the code in the worksheet_activate event (signature below) associated with the sheet containing B3 Private Sub Worksheet_Activate() 'Put your code here 'Let me know if you want me to do this 'put an IF around the line where earlier code assigns a value 'use that to change B3 to "" End Sub "SPISO" wrote: One more question. Is there a way to revise B3 in Dashboard to change to 0 the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert to today's date
I was thinking this weekend about my dashboard that you have helped me
create. Last week you wrote me a code that changed B3 to "" if the cell associated to today's date = "" in 'draw'. I would like to use that same blank cell in 'draw' to cause another action when the workbook opens. I would like cell Q27 in 'Dashboard' to change to the value associated to today in worksheet 'goals' if there isn't any value associated to today in 'Draw'. Would I just change "" to the sheet name and cell? Is that an easy modification to the code you have below? What would that look like? I did a side by side comparison to your two codes where one was caused by a cell change versus a workbook opening. They do look pretty similar but I am missing something when i combine the two to get the macro to input a cell value versus "". Right-click on the Dashboard tab, choose View Code and paste the following either before or after the earlier macro Private Sub Worksheet_Activate() Application.ScreenUpdating = False Application.EnableEvents = False Sheets("Draw").Select Sheets("Draw").Columns("A:A").Select Selection.Find(What:=Date, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If ActiveCell.Offset(0, 1).FormulaR1C1 = "" Then Sheets("Dashboard").Cells(3, 2) = "" End If Sheets("Dashboard").Select Application.EnableEvents = True Application.ScreenUpdating = True End Sub 'Note: Application.EnableEvents has to be turnedoff (as done in the code) so that same event is not fired again and again as the code reads the other sheet "SPISO" wrote: I understand why I should not put a zero in B3. That makes since. I am going to have to plead ignorant on what you were talking about with the code in worksheet_activate event comment though. I am going to need help with that. You have been such a life saver. "Sheeloo" wrote: You have got the right idea... However make B3 empty. If you change it to 0. 0 will be populated against the date... You can write the code in the worksheet_activate event (signature below) associated with the sheet containing B3 Private Sub Worksheet_Activate() 'Put your code here 'Let me know if you want me to do this 'put an IF around the line where earlier code assigns a value 'use that to change B3 to "" End Sub "SPISO" wrote: One more question. Is there a way to revise B3 in Dashboard to change to 0 the first time the workbook is opened any given day? I am thinking something like if the cell associated to the date in 'Draw' is empty, make B3 = 0. Then once B3 is changed it will insert a value in the associated date in 'Draw' from your previous macro preventing B3 from changing to 0 again that day. Would that work? "Sheeloo" wrote: You are most welcome. That is what computers are meant for.... saving us from tedious work. "SPISO" wrote: Thanks So Much. It worked. What a time saver. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
How to use Today's date in a cell and make it stay the same date | Excel Discussion (Misc queries) | |||
I need today's date returned as date format in formula | Excel Discussion (Misc queries) | |||
Count number of cells with date <today's date | New Users to Excel | |||
count the number of cells with a date <= today's date | New Users to Excel |