Forum Discussion
VBA or Excel Formula?
Ultimately, I would like the formula to calculate the Job Order ID numbers. Currently, there are 5 attributes to sort by, and the attributes selected are determined by a 0 (do not include in the formula) or a 1 (include in the formula) from the information in C24:D28. Instead of putting the "long" version of each attribute into the formula, I tried substituting ($C$24=1,$D$24,""), etc. to make a "short" version. Then, in the "final" version, I want to combine 3 cells to run the formula and return the answer (the Job Order ID numbers).
Long version:
{=IFERROR(TEXTJOIN(", ",TRUE,IF((Table_JobOrders[2DigZip]=INDEX(CandTable[2DigZip],MATCH([@CandidateID],CandTable[candidateID],0)))*(Table_JobOrders[correlatedCustomText1]=INDEX(CandTable[Category name],MATCH([@CandidateID],CandTable[candidateID],0)))*(Table_JobOrders[customText20]=INDEX(CandTable[customTextBlock4],MATCH([@CandidateID],CandTable[candidateID],0)))*(Table_JobOrders[status]=INDEX(CandTable[status],MATCH([@CandidateID],CandTable[candidateID],0)))*(Table_JobOrders[type]=INDEX(CandTable[type],MATCH([@CandidateID],CandTable[candidateID],0))),Table_JobOrders[jobOrderID],"")),"")}
Short version:
=IFERROR(TEXTJOIN(", ",TRUE,IF($C$24=1,$D$24,"") &"*" & IF($C$25=1,$D$25,"")&"*" & IF($C$26=1,$D$26,"")&"*" & IF($C$27=1,$D$27,"")&"*" & IF($C$28=1,$D$28,""),Table_JobOrders[jobOrderID],""),"")
Final version: =B30&B31&B32 [This returns the equation, but does not "run" the formula}
It is the "final" part of the formula where I am having trouble in that I don't understand how to make the formula "run."
Any help is appreciated!
Thank you!
Ronna
Ronna,
If I understand correctly, you have a cell that you have entered =(B30&B31&B32), and when you press enter, instead of showing the result, it continues to display the formula. It's likely that the cell is formatted as text. If you change it to General, then click in the formula bar or double click the cell, then press enter, does it resolve your issue?
Bob, thanks for your response. I tried your suggestion of making the cells as "general" and not "text" and it didn't work. :(
Any other ideas are welcome!
Thanks,
Ronna
My other thought would be on the Formulas tab, ensure that Show Formulas is not enabled. Beyond those two possibilities, I would need to see a 'sanitized' version of your file, without any personal or sensitive information, to see what might be going on. If you could post a copy, I might be able to assist a little better.
I checked the Formulas tab, and "Show Formula" is NOT enabled.
I've attached my workbook for you look through at your convenience. There are 3 tabs and I'm having trouble on the "Cross Submit" tab getting the formula to "run."
Let me know if I can clarify anything for you.
Thank you!
Ronna
