Forum Discussion
VBA or Excel Formula?
Good morning! I have a spreadsheet where I am trying to combine information from 3 cells into one working formula. I have a formula that puts the pieces together, but I need help figuring out how to run the formula. I tried copying / adapting an earlier VBA formula using EVAL, but can't get it to work here. I've included my spreadsheet so you can see what I'm referring to, but here's a quick snapshot.
| 1 | ((Table_JobOrders[2DigZip]=INDEX(CandTable[2DigZip],MATCH([@CandidateID],CandTable[candidateID],0))) |
| 0 | (Table_JobOrders[customText20]=INDEX(CandTable[customTextBlock4],MATCH([@CandidateID],CandTable[candidateID],0))) |
| 1 | (Table_JobOrders[correlatedCustomText1]=INDEX(CandTable[Category name],MATCH([@CandidateID],CandTable[candidateID],0))) |
| 0 | (Table_JobOrders[customFloat2]=INDEX(CandTable[customFloat2],MATCH([@CandidateID],CandTable[candidateID],0))) |
| 1 | (Table_JobOrders[Results3]=INDEX(CandTable[Results3],MATCH([@CandidateID],CandTable[candidateID],0))) |
| IFERROR(TEXTJOIN(", ",TRUE,IF( | BEGINNING PART OF FORMULA |
| ($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],"")),"") | ENDING PART OF FORMULA |
I'm not sure what formula to use, or if what I want to accomplish requires the use of VBA.
Thanks!
Ronna
- Your question cannot be answered without a clear description of what it is the formula is supposed to calculate.
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?
