Use Cell Value in Formula

[Steve 1962]

Hi

Just require a change to a formula in column I.

I want each formula in column I to use the text in cell I1, Cell I2 works fine if I "hard-code" the contents of cell I1 into the formula. However, when I attempt to reference cell I1 into the formula in cell I3, it doesn't work.

Is there a change to the formula that I can use so that I don't have to go and retype the formula in two areas - it just automatically references the value in cell I1 ?

Thanks

Steve

Book1
	A	B	C	D	E	F	G	H	I
1	Allocation Ds	Comment	Starttime	Endtime	Minutesworked	Startlocation			WU2Y
2	SERVICE ID WU2YPASS RELIEVE	SSl33 ldd777	01/08/2018 14:00	01/08/2018 23:59	599	Crew	01/08/2018 00:00	2:00:00 PM	599
3	SERVICE ID WU2YPASS RELIEVE	SSl33 ldd777	01/08/2018 14:00	01/08/2018 23:59	599	Crew	01/08/2018 00:00	2:00:00 PM
Sheet

Cell Formulas
Range	Formula
I2	=IF(D2="","",IF(SUM(COUNTIF($B2,"*WU2Y*")+COUNTIF($A2,"*WU2Y*"))>0,$E2,""))
I3	=IF(D3="","",IF(SUM(COUNTIF($B3,I1)+COUNTIF($A3,I1))>0,$E3,""))

 

[Aladin Akyurek]

Looks like...

=IF(D2="","",IF(SUM(COUNTIF($B2,CHOOSE({1,2},$B$2,"*"&$I$1&"*"))),$E2,""))

 

[cunningAce]

Does this do what you need?

=IF(D3="","",IF(SUM(COUNTIF($B3,"*"&$I$1&"*")+COUNTIF($A3,"*"&$I$1&"*"))>0,$E3,""))

 

[Steve 1962]

How does this formula check the value in A2 (not just cell B2) ??

 

[Aladin Akyurek]

How does this formula check the value in A2 (not just cell B2) ??

Which suggestion are you addressing? In case you didn't see, have also a look at post #2 ?

 

[Steve 1962]

Thanks Cunning Ace. Works fine.

 

[cunningAce]

Your Welcome.