Change cell colour by formula

**johnandrews** · 12-12-2013, 11:55 AM

How do I get a logical formula to add text to a different cell?

I'm checking a large spreadsheet redflagging things in different columns. I want another column telling me if there's any red flags at all in a row.

eg. I need a the NAME (column A) to say something. Column B has

Formula:

Please Login or Register  to view this content.

If it's blank I know about it. How do I get the same formula to add text to something in column R? The object is with several formulas contributing to it I have a summary of redflags. Can post a mockup to explain if it helps.

**FDibbins** · 12-12-2013, 12:10 PM

A formula only works on the cell it is in, it cannot "push" anything to another cell (unless of course that other cell has a formula that references the 1st cell).

Have you tried using Conditional Formatting to make cells change color based on their contents?

**johnandrews** · 12-12-2013, 12:30 PM

I've figured conditional formatting, but can you format colour in one cell according to the contents of ten other cells? That's what I'm trying to accomplish.

**FDibbins** · 12-12-2013, 12:42 PM

Yes you can, you use "use formula" for that

Give it a shot, and let me know how you make out. If you have a problem, upload a sample workbook

**johnandrews** · 12-13-2013, 04:42 AM

test.xlsx
Here's a sample. I want columns B, D and F to report to column G if A, C and E are blank.

**johnandrews** · 12-13-2013, 06:58 AM

Ah. I misread, FDibbins. I have no idea how to do so - I would like someone to show me how a formula can put text into another cell.

**FDibbins** · 12-13-2013, 02:37 PM

Thanks for the file. Sorry, Im not exactly sure what you are trying to achieve? Could you provide a few sample answers and show how you got them?

**popipipo** · 12-13-2013, 02:59 PM

maybe this:
=COUNTIF(A3:F3;"mistake")&" mistake(s)"

**johnandrews** · 12-13-2013, 04:01 PM

test.xls

FDibbins: attached is the text of what I'm trying to achieve except I want a formula to do it automatically.
Popipipo: sorry mate, the formula doesn't work (at least in Mac Numbers which I only have access to).

**popipipo** · 12-13-2013, 04:24 PM

=IF(COUNTIF(B3:F3,"mistake")>0,"mistake","OK")
Is this better?

**johnandrews** · 12-13-2013, 04:30 PM

Evidently.

I was going to thank you in your presumably native Dutch, but I realised that was the relatively unexciting 'danke u.' I'll opt for Zalig kerstfeest en Gelukkig Nieuwjaar instead.