Conditional formatting on cells containing formulas

[Finalfrontier1976]

Hi,
I have an issue trying to get a conditional formatting set up on returned values from formulas. I have a worksheet with formulas in it that return values from another worksheet in the same workbook. I want to keep the formulas in the cells but impose a CF based on the value of the percentage that the formulas are bringing back. For example, if he returned value is between 0% and 5% then fill the cell green.

So far no success. Any ideas out there?

[davo3286]

Can you upload the spreadsheet to make it easier to understand what you're wanting?

[sandy666]

mybe try with ISFORMULA (if exist in 2013 - I don't remember that) as a base

isformula.jpg

[Finalfrontier1976]

Can you upload the spreadsheet to make it easier to understand what you're wanting?

I'll do it now. The sheet is called North and it's the percentages that are returned via the formula. I want to conditional format the values but keep the formulas in the cells.

[Finalfrontier1976]

Can you upload the spreadsheet to make it easier to understand what you're wanting?

Here it is

[FDibbins]

To start with, your "percentages" are text, not numbers, you need to convert them 1st (if you look in the data sheets, you will see a small green triangle at the top of each cell in F - hover over it to see what the message is)

You can fix this fairly easily by starting the vlookup with --....
=IFERROR(--VLOOKUP($A12,INDIRECT("'"&B$11&"'!A:F"),6,FALSE),"")
Format the cell as %

Then, you should avoid using full-column references in INDIRECT, it will slow your file down. So maybe consider this, instead...
=IFERROR(--VLOOKUP($A12,INDIRECT("'"&B$11&"'!A1:F1000"),6,FALSE),"")

OK now that you have real numbers in your cells, you can apply CF based on those values. I see that your formulas in NORTH go down to row 5000 - do you really need that many? CF tends to become resource- hungry when used in large volumes like that, and will also slow your file down

1. highlight the range you want to apply the conditional formatting to (say
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =B12<=.05
format fill as needed

[dominicb]

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

[kaustubh123]

Mr. FDibbins,

Nicely explained.

Regards,
Kaustubh

[FDibbins]

Mr. FDibbins,

Nicely explained.

Regards,
Kaustubh

Thanks for the kind words