Formula if one or more the cells within a selection of cells contains text then change tex

[Johan1984]

Hi All,

Its been a while since i was here.
I hop you all are okay.

I am working on a daily management sheet.
I am looking for a function or formula that changes a cells values when one or more cells of a selection of cells has a certain values.

For example:

If one of the cells in range (F2:F8) has text "one" cell Z1 changes into text "Two".

I hope its clear to some one. As i said my excel is a bit rusty at the moment.


Thanks in advance,

Johan

[JohnTopley]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[Johan1984]

My Apologies,

I created a small workbook to explain.
What i would like to accomplish:
On employee sheet you can select a color per employee per day. And also for managers.

if on day 1 all employees are green, on the dashboard employees day 1 should be green,
if one or more of the employees is orange and the rest is green, on the dashboard it should be orange,
if one or more employees are red, on the dashboard it should show red.

I hope this makes sense.\

If all went well, i added the workbook.

Thanks,

Johan

[Estevaoba]

Maybe something like:
=IF(COUNTIF(F2:F8,"*ONE*"),"TWO","")

[Estevaoba]

Now I see your example. Try this in B2 and copy across:

Please Login or Register  to view this content.

Good luck!

[Johan1984]

Unfortunately i get this error. Am i doing something wrong or is this an issue with my excel version.

[JohnTopley]

You may need to change "," (comma) to ";" (semi-colon) in the formula

[Johan1984]

It works like a charm, thanks so much, got one more adjustment.

In this formula it look to a fixed group of cells, now id like the same function but instead of using a fixed group of cells,
i want to use rows when cell A of that row had a certain text in it.

In my test workbook i added a column A (People) so i want the formula to use the row of where Cell A had the value Engineer instead of a fixed group of cells.

i hope this makes sense.

Many thanks in advance again.

With kinds regards,

Johan

[Estevaoba]

You're welcome.
I changed to COUNTIFS, so as to include the new column and expand the range:

Please Login or Register  to view this content.

It would be smart to enter text Engineer in a cell and use that cell as reference in the formula, so you can quickly check for other titles.

[Johan1984]

Hey,

Thanks for all the support, got a question again, in the attachment document, i want the yellow part the stay where it is at all times, so both the rows and columns. I tried this with freezing panes, but them its always one of the two panes that frezze.
I am makes a files where the page layout should stay where it is and the data cells can move in both directions.

Anyone an idea?

Many thanks

Johan