[Solved] Using the IF command

[MikeJP]

Hello.
I have what I believe is a simple query, but I cannot find the correct combination of formula commands to make it work; can you help?

I am using this formula in cell G1 =IF(BA5="Duplicate";"ALERT") to make cell G1 show the text ALERT, this works well and I have a Conditional Format running to show the G1 background as RED; but, I now wish to expand BA5 to include the rest of the cells in column BA. So, I am expecting to insert BA5:BA9999 into the formula, but cannot get it right.

What am I doing wrong please?

[RusselB]

I think you need to enter the formula as

Code: Select all

=if(BA5:BA9999="Duplicate";"ALERT")

and then press Ctrl+Shift+Enter so the formula is entered as an array formula

[MikeJP]

Thanks for the prompt reply, but it does not work. It returns the message #VALUE!

[RusselB]

This usually means that the formula wasn't entered as an array formula.
You can't just copy and paste the formula I gave you, as the Enter key is included by default, however you need to end the formula with Ctrl+Shift+Enter

[MikeJP]

Sorry, I did do that. But have now entered the suggested formula using the Function Wizard fields, and hit Control, Shift, Enter whilst the cursor was flashing after the T" of "ALERT". It still returns #VALUE!

[MrProgrammer]

I am using this formula in cell G1 =IF(BA5="Duplicate";"ALERT") to make cell G1 show the text ALERT, this works well and I have a Conditional Format running to show the G1 background as RED; but, I now wish to expand BA5 to include the rest of the cells in column BA.

Is the goal to show ALERT in G1 if any cell in BA5:BA9999 contains "Duplicate"? If so I suggest =IF(SUMPRODUCT(BA5:BA9999="Duplicate");"ALERT";""). I did not test that because you did not attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

Using the IF command

There are no commands in Calc. IF() is a function.

[Lupp]

Sorry. I think the first step should be to make clear what the OQ is trying to achieve.

-1- What shall the formula return if the condition comes out FALSE? The respective part is missing. Calc formulas are not "commands" that can simply do nothing.
-2- Shall the formula return the "ALERT" cel by cell to many cells, each one associated by its position to one of the cells of BA5:BA9999?
OR
-3- Shall the alert be raised if at least one of the cells in the referenced range has "Duplicate" in it?

(Editing:)
Sorry. Posts crossed.

[MikeJP]

Thank you; I will try your suggestions tomorrow.

The aim of this formula is simply to show ALERT in G1 if the word Duplicate is shown anywhere in the Column BA cell array; this is if there is just one "Duplicate", or many. I have a formula in the Col BA cells looking for duplicate numbers showing in another column; if a duplicate number is seen; it puts the text "Duplicate" alongside in Col BA.

The s/sheet is very wide and 800 rows deep; I wanted a simple ALERT flag to show when a duplicate is found off screen.

Thanks for your help.

[MikeJP]

Thank you to all who offered help; it is much appreciated.

The SUMPRODUCT formula from Mr Programmer solved the problem - thanks.