conditional formatting and AND OR and INDIRECT functions

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

To start with, I am able to replicate the problem. The second formula works as a worksheet formula, but I do not know why it does not work as a conditional formatting rule.

I suggest you give us the fully complicated version. Because I would use this conditional formatting formula in B1:

Formula:

Please Login or Register  to view this content.

or even just use the "Format only cells that contain" rule instead of a formula. Using that INDIRECT formula is very convoluted. And the second one adding an AND is pointless.

I think if you tell us what you are actually doing we can suggest a more comprehensive solution. However, in the meantime I will continue to see if I can find out what's going on with the formula you have presented.

Are you really in England? If so, I wonder why you are using a European locale?

=AND(INDIRECT(ADDRESS(ROW();COLUMN()))=7;TRUE)

For a UK locale, the formula separators should be commas.

Please update your profile as requested above and provide a sample workbook based on post #3.

You do not need to post any XML or any links.

Read the yellow banner at the top of the page, which tells you how to attach your workbook using our attachments feature.

Once you have provided a workbook, I'll take a look. Remember: we want to see this in its more complex form.

Sorry, but you are wrong. You have always been able to post an attachment - there is not and never has been any restriction on this. However, you won't be able to post any links until you have made at least TEN posts. Something in the post (not the attachment) will have triggered the message.

And yes, that message is what I am getting trying to post that Excel FILE following the yellow banner instructions.

No, that message is NOT triggered when trying to attach a file - only if you are trying to include a link, or something that the forum software thinks is a link. You will find many examples of members whose first post includes an attachment. There is nothing different about your account.

I shall have a look at the workbook now.

OK, so this works:

=AND(B1=7,1=1)

You have not yet explained why you feel that the INDIRECT is necessary. We asked to see the wider picture, but you have not shared it.

Originally Posted by AliGW

No, that message is NOT triggered when trying to attach a file

I wonder if this occurs if you attach the file using a link, rather than an attachment icon. I have never done that myself but I see a lot members do it that way. I'll test that out.

EDIT: No, that wasn't it. Even if you are not allowed to post links, you can post an attachment as an inline link.

I think there was something in the body of the post OR the file's name that was triggering the message. It will not have been the file per se.