Dates

[Taezalium]

I have a spreadsheet that is constantly updating itself from the internet.
The spreadsheet has a list of 30 people and their respective data from an
online game.
I am trying to make a column labelled "Last Played", I would like this
column to automatically update, unfortunately the information available from
the internet does not show an entry for the last time a player logged on. The
only way I can tell is if their score changes. So in column "AH" I have all
the players overall scores (updated automatically every 24 hours), in column
"AJ" I have any points gained since column "AH" was updated (updated
automatically every 2 mins).
If in column "AN" (my "Last Played" column) I put the formula
=IF (AJ1>0,today(),)
then today's date is shown in cell "AN1†that works nicely.. my problem is
if "AJ1" = 0, (showing that a certain player has gained no points that day)
how can I keep the date shown in the cell "AN1" to show that date that their
points increased, i.e. show the last date that "AJ1" did not equal 0.

 

[Archimedes' Lever]

I have a spreadsheet that is constantly updating itself from the internet.
The spreadsheet has a list of 30 people and their respective data from an
online game.
I am trying to make a column labelled "Last Played", I would like this
column to automatically update, unfortunately the information available from
the internet does not show an entry for the last time a player logged on. The
only way I can tell is if their score changes. So in column "AH" I have all
the players overall scores (updated automatically every 24 hours), in column
"AJ" I have any points gained since column "AH" was updated (updated
automatically every 2 mins).
If in column "AN" (my "Last Played" column) I put the formula
=IF (AJ1>0,today(),)
then today's date is shown in cell "AN1” that works nicely.. my problem is
if "AJ1" = 0, (showing that a certain player has gained no points that day)
how can I keep the date shown in the cell "AN1" to show that date that their
points increased, i.e. show the last date that "AJ1" did not equal 0.

=IF(one criteria,"show this", otherwise show this)

 

[MyVeryOwnSelf]

I have a spreadsheet that is constantly updating itself from the

internet. The spreadsheet has a list of 30 people and their respective
data from an online game.
I am trying to make a column labelled "Last Played", I would like this
column to automatically update, unfortunately the information
available from the internet does not show an entry for the last time a
player logged on. The only way I can tell is if their score changes.
So in column "AH" I have all the players overall scores (updated
automatically every 24 hours), in column "AJ" I have any points gained
since column "AH" was updated (updated automatically every 2 mins).
If in column "AN" (my "Last Played" column) I put the formula
=IF (AJ1>0,today(),)
then today's date is shown in cell "AN1†that works nicely.. my
problem is if "AJ1" = 0, (showing that a certain player has gained no
points that day) how can I keep the date shown in the cell "AN1" to
show that date that their points increased, i.e. show the last date
that "AJ1" did not equal 0.

One way is to use a circular reference.

Before starting, enable circular references by checking the box at
Tools > Options > Calculation > Iteration

Then use a formula in AN1 like
=IF(AJ1>0,TODAY(),IF(AN1=0,"",AN1))

Caution: allowing circular references can be a risk. Later, if somebody
adds a circular reference in the workbook by mistake, Excel won't flag it
as an error.

 

[Yvonne007]

I think that you should use macro better than using function.
I have a sample macro.

Sub macro1()
Dim a As Double
On Error Resume Next
a = Range("AN1").Value
If Range("AJ1").Value > 0 Then
Range("AN1").FormulaR1C1 = "=today()"
Else: If Range("AJ1").Value = 0 Then Range("AN1").FormulaR1C1 = a
End If
End Sub

I wish that it helps you.

 

[BSc Chem Eng Rick]

Hi T

Use a circular reference:

=IF(AJ1>0,TODAY(),AN1)

You need to enable iterative calculation for this to work. Let me know if
you need help to do this.

If this helps, please click "Yes"
<><><><><><><><><><><>