formulae works in one cell but not another


I have had this issue so far with conditional formatting and OFFSET formulae.

works fine where applied in the spreadsheet but stubbornly refuses to apply in random cells.

the only solution is to delete the entire row or column and insert a new one in its place, copy/paste the relevant data and the formula works!

If I have copied a sheet in order to use as a template for another version, the same cell in the copies has the problem.

i have spoken to a colleague who has found similar issues in his own work with different conditional formulae.

Does anyone else have experience of this?



Selected Answer

It sounds like an issue with absolute versus relative cell references.

Try making your cell references absolute before putting them into the conditional formatting feature and see if that helps. 

Otherwise, please update your question to include the problem formula.

Also, often times, when editing a formula from the conditional formatting window, it changes without you noticing; that's why you should always change these formulas within a cell first and then copy/paste them into the conditional formatting window.



thanks for the thought, but I dont think that is the problem.
where the issue happens with a formula, such as OFFSET, the same formula will work in 9 rows out of 10.
on the misbehaving row, I can copy in data from a working row and it still does not work. only solution is to copy the data to a blank row, delete the entire row, insert a new row, paste back the old data.
for conditional formating, the conditions are that a cell colours amber for a date within 60 days and red for a date within 30 days. I have a matrix of dates where all works fine, except in a couple of columns. to fix this I have to employ the copy, delete, insert, paste routine.
all a bit frustrating.
DCR (rep: 2) Feb 26, '17 at 6:32 am
Update your question with the formula that causes the issues or attach a sample spreadsheet and then we can see what's going on.
don (rep: 1785) Feb 26, '17 at 10:02 am
(Also, you don't have to select the Answer until it solves your issue.)
don (rep: 1785) Feb 26, '17 at 10:03 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login