formula sequence

[JuniorB]

Hello,

I'm looking for the right formula sequence possibly using IF, with VLOOKUP and conditional formatting.

If I have a name in cell A1 with B1 empty (sheet1), where if a date is entered in B1, the name in A1 is searched across sheets 2, 3, 4, and 5 and wherever that name is located, the font would change to red. When the date is deleted beside the name, the sheets would return to the original font colour.

Thanks for any advice or tips, I appreciate it.

Take care,

JuniorB

 

[JGordon11]

Can be done with a formula in conditional formatting. Example below. I think you would need to define the conditional formatting four times, once each for sheets 2-5. In order not to bog down excel, the conditional formatting range should be limited to the data or perhaps a little bigger if you expect the data to grow. Definitely do not apply conditional formatting to the entire sheet. In the example below, the CF is only applied to a 20 cell range on Sheet2. If your date field is text and not a true excel date (which is a number) then change the formula to =(A1=Sheet1!$A$1)*(LEN(Sheet1!$B$1)>0)

Book2
	A
1	Adam Ant
2	Bruce Banner
3	Charlie Chaplin
4	Delta Dawn
5	Emilio Estevez
6	Fannie Farmer
7	Galileo Galilei
8	Harry Houdini
9	Igor Ivanov
10	Jonah Jameson
11	Kris Kristofferson
12	Lois Lane
13	Mickey Mantle
14	Nick Nolte
15	Ozzy Osbourne
16	Pol Pot
17	Quintavius Quire
18	Ryan Reynolds
19	Sue Storm
20	Tina Turner
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=(A1=Sheet1!$A$1)*(Sheet1!$B$1>0)	text	NO

Book2
	A	B
1	Mickey Mantle	12/3/2021
Sheet1

 

[Toadstool]

Hi JuniorB,

This is Sheet1

JuniorB.xlsx
	A	B	C
1	Harry	1/1/2020
2
Sheet1

Here is the Conditional Format for every other sheet

JuniorB.xlsx
	A	B	C	D	E	F	G
1
2
3				Vera		Harry
4
5			Fred		Bert
6
7				Tom
8
9	Harry					Vanessa
10
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1048576	Expression	=AND(A1=Sheet1!$A$1,ISNUMBER(Sheet1!$B$1))	text	NO

 

[JuniorB]

Hi - Thanks so much for the help. Both solutions work but I should have expanded my description more, sorry, I went too simple in trying to understand.

If I have a range of names (Sheet1), how can those formulas in Sheet2 work? And what if there's another column of names for Sheet 1 in addition to the A column, how would the formula work? (here's an example)

Thanks alot for the support. Really appreciate it.

 

[Toadstool]

Data

JuniorB.xlsx
	A	B	C	D
1	Adam Ant		Mickey Mantle
2	Bruce Banner		Nick Nolte	2/2/2021
3	Charlie Chaplin	1/1/2021	Ozzy Osbourne
4	Delta Dawn		Quintavius Quire
5	Emilio Estevez		Ryan Reynolds	3/3/2020
6	Fannie Farmer		Sue Storm
7	Galileo Galilei		Tina Turner
8	Harry Houdini		Delta Dawn
9	Igor Ivanov
10	Jonah Jameson
11	Kris Kristofferson
12	Lois Lane
13
Sheet3

Sheet with CF

JuniorB.xlsx
	A	B	C	D	E	F
1				Harry Houdini
2		Charlie Chaplin		Igor Ivanov
3
4
5		Charles Babbage		Nick Nolte
6
7		Ryan Reynolds
8
9
10
11
12
13
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:H14	Expression	=OR(IFERROR(VLOOKUP(A1,Sheet3!$A$1:$B$12,2,0),0)>0,IFERROR(VLOOKUP(A1,Sheet3!$C$1:$D$12,2,0),0)>0)	text	NO

 

[Peter_SSs]

Another option

JuniorB.xlsm
	A	B	C	D
1	Adam Ant		Mickey Mantle
2	Bruce Banner		Nick Nolte	2/02/2021
3	Charlie Chaplin	1/01/2021	Ozzy Osbourne
4	Delta Dawn		Quintavius Quire
5	Emilio Estevez		Ryan Reynolds	3/03/2020
6	Fannie Farmer		Sue Storm
7	Galileo Galilei		Tina Turner
8	Harry Houdini		Delta Dawn
9	Igor Ivanov
10	Jonah Jameson
11	Kris Kristofferson
12	Lois Lane
13
Sheet1

JuniorB.xlsm
	A	B	C	D
1				Harry Houdini
2		Charlie Chaplin		Igor Ivanov
3
4			Charlie Chaplin
5		Charles Babbage		Nick Nolte
6
7		Ryan Reynolds
8
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:D7	Expression	=SUMPRODUCT((Sheet1!$A$1:$C$20=A1)*(Sheet1!$B$1:$D$20>0))	text	NO

 

[JuniorB]

Hello,

Thank you to everybody for the help. Really appreciate it and those solutions worked. Take care and have a wonderful holiday season!

 

[Toadstool]

You're welcome.