# Conditional formatting using formulas referencing another worksheet

I’m new to LibreOffice. I have a huge spreadsheet that I made in excel 2010 but need it to function in Calc since many of those that will use it have Libre on their machines. Many of my conditional formats didn’t transfer so I’m working feverishly to understand the differences in Calc

I have a conditional formula in excel that is =AND($C10=“Combining two cases”,’FNL LIMIT’!$C9=“”) If C10 on the current sheet says Combining Two cases and C9 on the other worksheet FNL LIMIT is blank A12 turns brown and so they can see the warning message I have built in white font

Once the cell on the FNL LIMIT worksheet is filled in the brown needs to go away. I can’t for the life of me get this to work in Calc. It works beautifully in excel but Calc i cannot get it.

I can get the A12 cell to turn brown if C10 on the sheet I’m on says Combining two cases. The problem is when C9 on the other sheet is filled in, it is still brown and doesn’t turn off.

Please help me figure this one out. I know I’ll have several more as I test this workbook in Calc.

Thank you!

edit retag close merge delete

Sort by » oldest newest most voted

This works in LibreOffice.

=AND($C10="Combining two cases",'FNL LIMIT'.$C9="")


The formula you posted has! and smart quotes (“ and ”) instead.

more

Doesn’t work Why a period after the ‘FNL LIMIT’

I tried that. Got a ref# error Tried without it still the cell is brown.

more

The point is what separates the sheet part from the cell / range part in a reference.
(BTW: Why the difference of one in row numbers?)

( 2018-01-13 19:10:52 +0100 )edit

I got it to work The difference in row numbers is where the date field is. The sheet this goes on has a bunch of instructions and cover info. C10 is a drop down field to indicate a reason. If they indicate a certain reason the warning reminds them to go to the fnl limits sheet and fill it in. C9 on the fnl limits is a date field. This reminds them to go to that sheet. More is needed there but just getting them there and started is the point

( 2018-01-13 19:35:14 +0100 )edit

Quoting @Rockerdo: "I got it to work "
How?

( 2018-01-13 20:06:24 +0100 )edit

There are always ways to spoil a formula by rather hidden details. Without your sheet at hand I will probably not find out.
The attached example works for me in LibO Calc. If it also works for you look at every single caharacter comparing my formula with yours and your second sheet name with mine.
(BTW: Avoid spaces in sheet names. A tiny but annoying complication! Trailing or additional spaces are easily missed, biut can spoil everything.)

more