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?

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?

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?

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?

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?

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?

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?