Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates
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 (AJ10,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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates
On Sun, 18 Oct 2009 15:15:01 -0700, Taezalium
wrote: 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 (AJ10,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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates
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 (AJ10,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(AJ10,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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates
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. "Taezalium" wrote: 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 (AJ10,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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dates
Hi T
Use a circular reference: =IF(AJ10,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" <<<<<<<<<<< "Taezalium" wrote: 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 (AJ10,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel |