Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 13 Jan 2018 20:06:24 +0100Conditional formatting using formulas referencing another worksheethttps://ask.libreoffice.org/en/question/142967/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!Sat, 13 Jan 2018 17:48:08 +0100https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/Answer by Lupp for <p>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 </p>
<p>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</p>
<p>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. </p>
<p>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. </p>
<p>Please help me figure this one out. I know I’ll have several more as I test this workbook in Calc. </p>
<p>Thank you!</p>
https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?answer=142974#post-id-142974There 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](/upfiles/1515867901312699.ods) 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.)Sat, 13 Jan 2018 19:23:04 +0100https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?answer=142974#post-id-142974Answer by Rockerdo for <p>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 </p>
<p>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</p>
<p>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. </p>
<p>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. </p>
<p>Please help me figure this one out. I know I’ll have several more as I test this workbook in Calc. </p>
<p>Thank you!</p>
https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?answer=142971#post-id-142971Doesn’t work
Why a period after the ‘FNL LIMIT’
I tried that. Got a ref# error
Tried without it still the cell is brown. Sat, 13 Jan 2018 18:44:53 +0100https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?answer=142971#post-id-142971Comment by Rockerdo for <p>Doesn’t work
Why a period after the ‘FNL LIMIT’</p>
<p>I tried that. Got a ref# error
Tried without it still the cell is brown. </p>
https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?comment=142975#post-id-142975I 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 pointSat, 13 Jan 2018 19:35:14 +0100https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?comment=142975#post-id-142975Comment by Lupp for <p>Doesn’t work
Why a period after the ‘FNL LIMIT’</p>
<p>I tried that. Got a ref# error
Tried without it still the cell is brown. </p>
https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?comment=142973#post-id-142973Please 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?)Sat, 13 Jan 2018 19:10:52 +0100https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?comment=142973#post-id-142973Comment by Lupp for <p>Doesn’t work
Why a period after the ‘FNL LIMIT’</p>
<p>I tried that. Got a ref# error
Tried without it still the cell is brown. </p>
https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?comment=142977#post-id-142977Quoting @Rockerdo: "I got it to work "
**How?**Sat, 13 Jan 2018 20:06:24 +0100https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?comment=142977#post-id-142977Answer by Jim K for <p>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 </p>
<p>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</p>
<p>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. </p>
<p>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. </p>
<p>Please help me figure this one out. I know I’ll have several more as I test this workbook in Calc. </p>
<p>Thank you!</p>
https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?answer=142969#post-id-142969This works in LibreOffice.
=AND($C10="Combining two cases",'FNL LIMIT'.$C9="")
The formula you posted has`!` and smart quotes (`“` and `”`) instead.Sat, 13 Jan 2018 18:02:32 +0100https://ask.libreoffice.org/en/question/142967/conditional-formatting-using-formulas-referencing-another-worksheet/?answer=142969#post-id-142969