Ask Your Question

Conditional formatting using formulas referencing another worksheet

asked 2018-01-13 17:48:08 +0100

Rockerdo gravatar image

updated 2018-01-13 17:49:10 +0100

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-01-13 18:02:32 +0100

Jim K gravatar image

updated 2018-01-13 18:04:12 +0100

This works in LibreOffice.

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

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

edit flag offensive delete link more

answered 2018-01-13 18:44:53 +0100

Rockerdo gravatar image

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.

edit flag offensive delete link more


Please don't post comments as answers.
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?)

Lupp gravatar imageLupp ( 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

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

Quoting @Rockerdo: "I got it to work "

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

answered 2018-01-13 19:23:04 +0100

Lupp gravatar image

updated 2018-01-13 19:25:09 +0100

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.)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-13 17:48:08 +0100

Seen: 16 times

Last updated: 2 days ago