Formula > Text > Formula

[JerryGreen]

I am working on two sheets and I need help finishing a formula to text to formula function.

Sheet 1:
Cell H48 formula: =CONCAT(TEXTJOIN(",",TRUE,H2:H47),",""""",J46,)
Cell H48 output (Text):
IF(OR(ISNUMBER(SEARCH("Holiday",T4)),ISNUMBER(SEARCH("Cenex",T4)),ISNUMBER(SEARCH("Caseys",T4)),ISNUMBER(SEARCH("✰",T4)),ISNUMBER(SEARCH("✰",T4))),"Gas",IF(OR(ISNUMBER(SEARCH("Walmart",T4)),ISNUMBER(SEARCH("Dollar",T4)),ISNUMBER(SEARCH("✰",T4)),ISNUMBER(SEARCH("✰",T4)),ISNUMBER(SEARCH("✰",T4))),"Groceries",IF(OR(ISNUMBER(SEARCH("Subway",T4)),ISNUMBER(SEARCH("Dairy",T4)),ISNUMBER(SEARCH("✰",T4)),ISNUMBER(SEARCH("✰",T4)),ISNUMBER(SEARCH("✰",T4))),"Food & Drink","")))

Sheet 2: I need R3 to take the text from the H48 and output a changing formula. I can use T4 in sheet 2 to give me outputs such as Gas, Groceries, or Food & Drink. I understand I can fn 2, fn 9 to just paste the text as a formula. I want the formula to be mobile and changing as H48's output does, however, if possible. I have not been able to figure it out because of the "quotes" around the text function in the new formulas. My current attempt is =EvaluateString(CONCATENATE("=",Sheet1!H48)). The problem is it evaluates the entire string as a whole and not the separate if functions. I can get rid of the concatenate part too and add the = to cell H48 formula and output if anyone has ideas.

Thanks in advance.

 

[Dan_W]

Hi - I think people may be in a better position to help you if they can see what you're seeing. One way of doing that is my using the forum's xl2bb add-in - XL2BB - Excel Range to BBCode. It makes a huge difference.

More generally, I have a natural aversion to really long formulas because I'm easily lost. In those cases, I find it's best to break them down into smaller formulas, and use 'helper' cells (and even give those helper cells 'names') - I think your formula above would benefit from this because I can see the there is a lot nested somethings going on in there.

Finally, in case you haven't already come across it, one of my favourite tools when it comes to writing/making sense of formulas is Evaluate Formula:-

It will take you step-by-step through the formula evaluation process, showing how it gets to the final answer. I hope that helps.