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]

Sorry, I had the wrong formula, but even after changing, it's still not working. Still need your help. I need B8 to show "ACTUALS". See new image uploaded...

Thanks,

M

 

[Peter_SSs]

For the future I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. Also saves helpers having to manually type out sample data and formulas.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Could this be what you are after?

=IF(ISNUMBER(SEARCH("CONCATENATE",FORMULATEXT(C8))),"ACTUALS","FORECAST")

If not ..
Why should B8 show "ACTUALS" when C8 contains "BLUEGREEN"?
What is the logic the formula in B8 should use?

 

[Mexcel73]

I understand that my question my have been confusing and not set up the best.

The logic is that anytime the formula contains the word the word "concatenate", I would like column B to show "Actuals". This is regardless of what the results are in the concatenate formula. BLUEGREEN was just an example. It could have been daynight, redwhite... The reason this is important is because every week when I drag the formula to the new week (next column), it will have the concatenate formula. After dragging the formula with concatenate in it, I would like to avoid the extra step of typing in "Actuals". So when the word is hardcoded with "Concatenate" it works (as in cell C8), but when the word "Concatenate" is part of the formula it does not work, the results show "Forecast", but would like it to show "Actuals".

The formula you provided above did not work.

I hope this helps with clarifying as to what I'm looking for.

 

[Peter_SSs]

The logic is that anytime the formula contains the word the word "concatenate", I would like column B to show "Actuals".

The formula you provided above did not work.

So, what have I set up incorrectly in my sample worksheet or how have I wrongly interpreted what is written above?

If you need to give us any more sample data, please consider XL2BB as an image is not able to be copied from for testing and manual typing is time-consuming and prone to mistakes.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

24 10 26.xlsm
	B	C	D	E
8	ACTUALS	BLUEGREEN	BLUE	GREEN
9	FORECAST	BLUEGREEN
10	ACTUALS	RED854
Concatenate

Cell Formulas
Range	Formula
B8:B10	B8	=IF(ISNUMBER(SEARCH("CONCATENATE",FORMULATEXT(C8))),"ACTUALS","FORECAST")
C8	C8	=CONCATENATE(D8,E8)
C9	C9	="BLUE"&"GREEN"
C10	C10	="RED"&CONCATENATE(8,5,4)

 

[Mexcel73]

I am trying to attach the a file using Mini-Sheet, but I'm having trouble. I downloaded the Xl2bb add-on, but I don't see all the option as indicated.

I'll try again explaining with an image. See below:

In the Report tab, I would like to have a formula in G1 (and to the following tabs to the right), which will refer to G5. If G5 is pulling the data from the Actual tab then make G1 "ACTUALS". It's the same idea as my original question. Currently, after I drag the formula from F5 to G5, when actuals are available, I then have to go to the G1 and manually change it to "ACTUALS". I have forgotten to do this sometimes. This will avoid this extra step and in case I forget to do so.

Thank you in advance.

M

 

[Peter_SSs]

I am trying to attach the a file using Mini-Sheet, but I'm having trouble.

I don't think that you read my previous post very well.

(If you have trouble with XL2BB, ....."XL2BB Icons greyed out"...

In the Report tab, I would like to have a formula in G1 (and to the following tabs to the right), which will refer to G5. If G5 is pulling the data from the Actual tab then make G1 "ACTUALS". It's the same idea as my original question.

.. and my suggestion is the same idea as my previous suggestion, just using the word "Actuals" instead of "Concatenate" that you used in your original example

=IF(ISNUMBER(SEARCH("Actuals",FORMULATEXT(F5))),"ACTUALS","FORECAST")

A shorter alternative you could try though is

=UPPER(INDEX(TEXTSPLIT(FORMULATEXT(F5),{"=","!"}),2))

 

[Mexcel73]

Hi Peter,

First, thank you for taking the time to help me figure this out.

I'm not sure what I missed in your previous post, but let's put that on the side for now.

The formula with IF (ISNUMBER... didn't work.

However, your second suggestion with UPPER (INDEX... does work. Is there any way to modify this formula to specifically look for the words "Actuals" and "Forecast" rather than what's between the "=" & "!"? Because if the referenced tab had a different name, results would not show "Actuals" or "Forecast".

See below modification I made:

Thank you.

Michael

 

[Peter_SSs]

Could we have the sample shown above with XL2BB?
I have given information above about how to deal with the XL2BB icons being not available in the ribbon.

 

[Mexcel73]

I think I got it to work. See below screenshot: