Libre office question again

nice_lady, May 4, 11:01pm
Hi guys and girls I need a little assistance.

Have a spreadsheet which i have users input some data on a daily basis into certain cells, say for this example day 1 they will use "A1", day two they will use "A2" etc
I want to check that data against another cell 'D1", (this is always the 'check' cell, what does change in that cell is the data as it references another cell on a different sheet and the data does change daily)

IF the data in "A1" is <> than the data in "D1" then I need the font color of the data they have inputted to change to RED, (once they've hit enter of course), to indicate an error. They will then hopefully correct their data.
IF the data in "A1" is correct the font color will stay the default, (black).

That's easy enough to do with conditional formatting.
However as the data in "D1", (the 'check cell'), does change on a daily basis I want yesterdays data to NOT be 'checked' or the font changed
Same with tomorrows cell.


D1 value referenced from another cell is: 100
A1 value today as typed in by the user SHOULD be 100
User types 100 all is good and the font stays black.
User types a number <> than 100 and the font turns red
User re-types number correctly, (100), and the font reverts to black

D1 value as referenced from another cell is 200
A1 font does NOT change color
User types data into A2; system checks against D1 changes font to red if data incorrect. Font stays black if correct data.

At all times the only cell which might change color to red if the input is incorrect is TODAYS cell.
Yesterdays cells will always be black
Tomorrows cells will always be black

cat286, May 5, 2:15am
Will yesterday's A column cell always be correct before the next days entry?

nice_lady, May 5, 2:32am
thanks for looking

yes it'll be 'corrected' as the person who typing in the wrong info *should* see that it's wrong.

however day 1 cell A1
will be different info to day 2 cell A2

cat286, May 5, 3:46am
Well this is what I would do: you would need an absolute value for the first entry - in this example the value at A1 is entered as and absolute value in F1.
Then I would hide the columns with the additional formula.
The green cell D1 is the one that changes on a daily basis, if the cell below in column A is blank then it compares to Cell D1.

nice_lady, May 5, 4:01am
errmmm thanks will try to get my head around it lol

cat286, May 5, 4:10am
It took me a bit to get my head around it! In my photo, the blue formula relates to the entries in the column preceding it! And A9 is red as it demonstrates that it is not the same as D1

nice_lady, Dec 17, 3:57am
ok might take me till tomorrow to take a serious look - other things are needing attention now. Thanks.

Share this thread

Buy me a coffee :)Buy me a coffee :)