Conditional formatting with INDIRECT() function

Discuss the spreadsheet application

Conditional formatting with INDIRECT() function

Postby PeterNovotny » Mon Nov 25, 2019 12:55 pm

Dear all

I'm having troubles with conditional formatting and indirect. I have found some info in this thread, but it is old and I'm not sure if still valid also for my scenario. Now I'm creating new thread to see if there are any news or what can you recommend me as alternative.

Scenario is in attached file, but I will describe the steps:
1. In B1 enter any caption you like
2. In B2 enter any text for this caption
3. Now task si if B2 contains "x" color B1 in red, so go to B1 open format > Conditional formatting and enter formula:
Code: Select all   Expand viewCollapse view
NOT(ISERR(SEARCH("X";UPPER(INDIRECT(ADDRESS(ROW()+1;COLUMN());1)))))
, then click new and change background color. After all is complete click "OK"
4. If you change the text in B2 to add/remove x from this text you expect B1 to change background, but in reality the background is not changed!
5. To trigger background change you need to select B1 open format > conditional formatting and click OK, only in this case the formatting will change according to actual B2 content
6. BUT if you replace the formula in B1 with
Code: Select all   Expand viewCollapse view
NOT(ISERR(SEARCH("X";UPPER(B5))))
you will see the color change immediately when you add/remove x from B2

Not sure if this is bug or feature, but seems inconsistent to me.

Little bit of background: I need to update several files where each contains cca 30-100 fields like in my example, for this reason I have created the indirect version of formula thinking that I simply select all fields (CTRL+CLICK) and then ONCE click format > conditional formatting, enter formula + requested formatting and done. If I need to use direct addressing (like the working example) it is huge amount of clicking (~50+5 clicks per file VS ~50x5 clicks per file) :-)

Is this conditional formatting behaviour bug or feature? Is there any possible optimization how to enter for multiple selected fields address look one field below?

thank you for any help
Attachments
IndirectConditionalFormatting_sample.ods
(9.88 KiB) Downloaded 13 times
Last edited by MrProgrammer on Tue Dec 03, 2019 1:05 am, edited 1 time in total.
Reason: Edited title, was: Conditional formatting with indirect
OpenOffice 4.12 on Windows 7 and 10
PeterNovotny
 
Posts: 4
Joined: Mon Nov 25, 2019 12:02 pm

Re: Conditional formatting with indirect

Postby MrProgrammer » Mon Nov 25, 2019 6:03 pm

Hi, and welcome to the forum.

PeterNovotny wrote: Is there any possible optimization how to enter for multiple selected fields address look one field below?
When you use INDIRECT(ADDRESS(ROW()+1;COLUMN());1) it tells me that you don't understand the fundamental concept of relative references in spreadsheets. Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know. Another explanation with a bit more detail is in [Tutorial] How do I specify the formula for a column?. Just select all the cells to conditionally format, enter the relative formula for the active cell (the one with the black border), and apply the formatting.

PeterNovotny wrote:Not sure if this is bug or feature, but seems inconsistent to me.
With your complex conditional format formula in B1, it is difficult for Calc to deterime that a change to B2's content will trigger it since the formula does not contain "B2". Press ⇧⌘F9 on a Mac or Ctrl+Shift+F9 on other platforms to tell Calc to re-evaluate all of them and you'll see your format change appear. This action is not needed when you use relative references.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3984
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests