Log In
Register
Home
January 24, 2023
Offline
Hi,
I am using and index match formula in conditional formatting to colour cells; for some reason it wont run, it doesn't bring up an error but it does as for an apostrophe which then duplicates the equals sign.
formula as follows:
=INDEX(Availability,0,MATCH(D12,PoolDate,0))>0
It ask for a preceding apostrophe which I've done
'=INDEX(Availability,0,MATCH(D12,PoolDate,0))>0
But then the formula changes to this
="'=INDEX(Availability,0,MATCH(D12,PoolDate,0))>0"
Help
See Answer
Moderators
January 31, 2022
Offline
Technically the first formula is correct. But if it will lead to the correct result when used in conditional formatting (CF) is another question.
Though, the fact that Excel came up with the suggestion "to add an apostrophe" suggests that there is some kind of typo in the formula when you entered it. The apostrophe turns the incorrect formula into a text, allowing you to keep what you have typed so far. When you then correct whatever the error might be, remove the apostrophe and press Enter to have a working formula (hopefully).
So, using that last formula (with the apostrophe) in CF will indeed do nothing.
Please share your file or at least a few screenshots to clarify what you are dealing with.
Answers Post
1 Guest(s)
