Home

VBA or Excel Formula?

Highlighted
Deleted
Not applicable

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

10 Replies
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?

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

So, you are trying to duplicate Column D, but with a dynamic formula, right?  There are a few hurdles here.  First, on my side.  I'm using Office 2016, and the TEXTJOIN function is not available to me.  Second, Column D uses an array formula, you can tell by the {} around it.  You don't actually key in the brackets, Excel automatically adds them when you enter the formula by pressing CTRL+SHIFT+ENTER.  What you are trying to do, if possible, is well beyond my skill level.  I apologize for taking up your time.

This array formula seems to return an array of the numbers you are looking for, wrapping it in a TextJoin should work:
=IFERROR(INDEX(CandTable[jobOrderID],SMALL(IF(CandTable[candidateID]=$B$3,ROW(CandTable[candidateID]),""),ROW(CandTable[candidateID])-ROW(CandTable[[#Headers],[candidateID]]))),"")

Jan, thank you for your response.  Can you please explain / show how wrapping in a TEXTJOIN would make the formula work?

 

Thanks!

Ronna

From the top of my head:
=TEXTJOIN(", ",TRUE,IFERROR(INDEX(CandTable[jobOrderID],SMALL(IF(CandTable[candidateID]=$B$3,ROW(CandTable[candidateID]),""),ROW(CandTable[candidateID])-ROW(CandTable[[#Headers],[candidateID]]))),""))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies