If then formula referencing another formula

[Mexcel73]

Hello,

I am trying to get an If-Then formula to work when referencing another formula. To best explain it, I have uploaded an image. I need B8 to show "ACTUALS".

Thank you.

M

referencing another formula. To best explain it, I have uploaded an image. I need B8 to show "ACTUALS".

Thank you.

M

 

[Mexcel73]

MrExcel Question.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		ACTUALS	ACTUALS	ACTUALS	ACTUALS	ACTUALS	FORECAST	FORECAST	FORECAST	FORECAST	FORECAST	FORECAST
2
3	Week ending	9/22/2024	9/29/2024	10/6/2024	10/13/2024	10/20/2024	10/27/2024	11/3/2024	11/10/2024	11/17/2024	11/24/2024	12/1/2024
4
5	Revenue	$1,500	$1,000	$2,000	$2,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500	$1,500
Report

Cell Formulas
Range	Formula
C3:L3	C3	=B3+7
B5:F5	B5	='Actual Revenue'!B5
G5:L5	G5	='Projected Revenue'!G5

 

[Peter_SSs]

Thanks, that is much better!

Firstly, you said the ISNUMBER formula did not work. Here is that formula and the UPPER formula with the sheet names you originally gave - they both work.

Mexcel73.xlsm
	F	G
1	ACTUALS	FORECAST
2	ACTUALS	FORECAST
3	20/10/2024	27/10/2024
4
5	1500	1500
Report

Cell Formulas
Range	Formula
F1:G1	F1	=IF(ISNUMBER(SEARCH("Actuals",FORMULATEXT(F5))),"ACTUALS","FORECAST")
F2:G2	F2	=UPPER(INDEX(TEXTSPLIT(FORMULATEXT(F5),{"=","!"}),2))
F3:G3	F3	=E3+7
F5	F5	=Actuals!F5
G5	G5	=Forecast!G5

Now, with the new sheet names here is the ISNUMBER one modified ..

Mexcel73.xlsm
	F	G
1	ACTUALS	FORECAST
2
3	20/10/2024	27/10/2024
4
5	1500	1500
Report

Cell Formulas
Range	Formula
F1:G1	F1	=IF(ISNUMBER(SEARCH("Actual",FORMULATEXT(F5))),"ACTUALS","FORECAST")
F3:G3	F3	=E3+7
F5	F5	='Actual Revenue'!F5
G5	G5	='Projected Revenue'!G5

 

[Mexcel73]

That worked!!! Thank you so much, Peter!!!

 

[Peter_SSs]

You're welcome. Thanks for confirming.