Hi everyone, I need your help and here is my problem:
In Sheet named "Punch Clock", I have cell A1=Today() and cell E1=Time and
cell A3 = John

In Sheet1 I have from B6:B370 a range of dates and from E4:S4 a range of Names

In cells B6:B370 I have this formula
IF($B$6TODAY(),"",SUMPRODUCT(--('Punch Clock'!$A$3=$E$4)*--('Punch
Clock'!$A$1=B6),'Punch Clock'!$B$1))

and I have this code

Sub ConvertToValue1AftIn()
Dim cell As Range
For Each cell In Range("E6:E370")
If cell < Sheets("Punch Clock").Range("E1") Then
cell.Value = cell
End If
Next cell
End Sub

This code works only on the range of dates, is it possible to extend it to
concider also the range of names?.

Thank you in advance for your kind help

Look in the help index for OFFSET

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"albertmb" wrote in message
...
Hi everyone, I need your help and here is my problem:
In Sheet named "Punch Clock", I have cell A1=Today() and cell E1=Time and
cell A3 = John

In Sheet1 I have from B6:B370 a range of dates and from E4:S4 a range of
Names

In cells B6:B370 I have this formula
IF($B$6TODAY(),"",SUMPRODUCT(--('Punch Clock'!$A$3=$E$4)*--('Punch
Clock'!$A$1=B6),'Punch Clock'!$B$1))

and I have this code

Sub ConvertToValue1AftIn()
Dim cell As Range
For Each cell In Range("E6:E370")
If cell < Sheets("Punch Clock").Range("E1") Then
cell.Value = cell
End If
Next cell
End Sub

This code works only on the range of dates, is it possible to extend it to
concider also the range of names?.

Thank you in advance for your kind help

Hi Don,
Thank you for your reply, but from what I could understand the OFFSET does
not solve my problem. The SUMPRODUCT formula works ok for me the actual
problem I have is in the code. the code changes the formula result into
value ommitting the formula, but it is based on the date alone, I would like
to be based on the date and the name. What I failed to specify is that from
cells E6:L370 I have the same SUMPRODUCT formula corresponding to the names
in cells E4:S4 So I get the result in the corresponding cell refering to the
name and date.

Thank you once again
Albert

"Don Guillett" wrote:

Look in the help index for OFFSET

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"albertmb" wrote in message
...
Hi everyone, I need your help and here is my problem:
In Sheet named "Punch Clock", I have cell A1=Today() and cell E1=Time and
cell A3 = John

In Sheet1 I have from B6:B370 a range of dates and from E4:S4 a range of
Names

In cells B6:B370 I have this formula
IF($B$6TODAY(),"",SUMPRODUCT(--('Punch Clock'!$A$3=$E$4)*--('Punch
Clock'!$A$1=B6),'Punch Clock'!$B$1))

and I have this code

Sub ConvertToValue1AftIn()
Dim cell As Range
For Each cell In Range("E6:E370")
If cell < Sheets("Punch Clock").Range("E1") Then
cell.Value = cell
End If
Next cell
End Sub

This code works only on the range of dates, is it possible to extend it to
concider also the range of names?.

Thank you in advance for your kind help

If desired, send your wb to my address below along with this msg and
before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"albertmb" wrote in message
...
Hi Don,
Thank you for your reply, but from what I could understand the OFFSET does
not solve my problem. The SUMPRODUCT formula works ok for me the actual
problem I have is in the code. the code changes the formula result into
value ommitting the formula, but it is based on the date alone, I would
like
to be based on the date and the name. What I failed to specify is that
from
cells E6:L370 I have the same SUMPRODUCT formula corresponding to the
names
in cells E4:S4 So I get the result in the corresponding cell refering to
the
name and date.

Thank you once again
Albert

"Don Guillett" wrote:

Look in the help index for OFFSET

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"albertmb" wrote in message
...
Hi everyone, I need your help and here is my problem:
In Sheet named "Punch Clock", I have cell A1=Today() and cell E1=Time
and
cell A3 = John

In Sheet1 I have from B6:B370 a range of dates and from E4:S4 a range
of
Names

In cells B6:B370 I have this formula
IF($B$6TODAY(),"",SUMPRODUCT(--('Punch Clock'!$A$3=$E$4)*--('Punch
Clock'!$A$1=B6),'Punch Clock'!$B$1))

and I have this code

Sub ConvertToValue1AftIn()
Dim cell As Range
For Each cell In Range("E6:E370")
If cell < Sheets("Punch Clock").Range("E1") Then
cell.Value = cell
End If
Next cell
End Sub

This code works only on the range of dates, is it possible to extend it
to
concider also the range of names?.

Thank you in advance for your kind help