Is it possible for some kind of formula to determine if a cell value increased or decreased without making a copy of the cell’s value beforehand?
I have a cell A1 that gets updated every 15 minutes by a ‘link to external data’ and I want to color another cell B1 based on if A1 increased (green) from last time or if A1 decreased (red) or if A1 stayed the same (black)
Hi
Look at the conditional formating. Menu item: Format -> Conditional Formatting -> Manage.
For example I have a cell with a formula (data);
=IF(F6<>"",(NOW()-F6),"N/A")
This looks at F6 which is a date and compares to the date now in days.
Then I have three conditions;
Condition 1: If cell value is less than or equal to -20
Style name Good: Text color green
Condition 2: If the cell value is between -19 and 0;
Style name Warning: Text color Orange
Condition 3: If cell value is > 0
Style name Bad: Text color red and bold
So you just add your conditions, then in the drop down you can select pre-existing formats, or in my case created what I wanted.
In your case your going to have to store/stash the previous cell value somehow…?
That’s the problem, in your case DATE is a naturally changing value so each time you read it its different. In my case i have a financial spreadsheet and I ant to know if a cell increased or decreased from the last time it was updated.
As for making a copy, I’m going to go out on a limb and say it cant be done. What i mean is, sure you can manually copy it over someplace if you want to sit in front of your computer and do that every 15+1 minutes or try to setup some kind of timer to call a fn every x minutes, but that’s ify and Calc may not even have timers. Calc should have the old value of A1 after it updates A1 because i think you can Ctrl-Z to go back to it (maybe not.) I cant think of a way to set up a formula to activate on or just before the ‘link to external data’ updates
On Wed 22 Nov 2017 06:46:01 PM CST, erbenton wrote:
That’s the problem, in your case DATE is a naturally changing value so
each time you read it its different. In my case i have a financial
spreadsheet and I ant to know if a cell increased or decreased from the
last time it was updated.
As for making a copy, I’m going to go out on a limb and say it cant be
done. What i mean is, sure you can manually copy it over someplace if
you want to sit in front of your computer and do that every 15+1
minutes or try to setup some kind of timer to call a fn every x minutes,
but that’s ify and Calc may not even have timers. Calc should have the
old value of A1 after it updates A1 because i think you can Ctrl-Z to go
back to it (maybe not.) I cant think of a way to set up a formula to
activate on or just before the ‘link to external data’ updates
Hi
OK, then probably a macro with a listener is needed… if you right
click on the sheet tab and select sheet events there is one there called
“Content Changed” have a browse of existing ones to see if there is one
that fits the bill…
–
Cheers Malcolm °¿° SUSE Knowledge Partner (Linux Counter #276890)
openSUSE Leap 42.2|GNOME 3.20.2|4.4.92-18.36-default
If you find this post helpful and are logged into the web interface,
please show your appreciation and click on the star below… Thanks!
Thanks, I wasnt aware of those listeners at all. I’ll check them out in the next few days and report back