Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 410K Members
- 9,745 Online
- 466K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- VBA or Excel Formula?

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Deleted

Not applicable

10-17-2018 07:59 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 07:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 09:08 AM

Your question cannot be answered without a clear description of what it is the formula is supposed to calculate.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 09:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 10:22 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 11:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 11:58 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 12:09 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2018 01:31 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-18-2018 01:48 AM

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]]))),"")

=IFERROR(INDEX(CandTable[jobOrderID],SMALL(IF(CandTable[candidateID]=$B$3,ROW(CandTable[candidateID]),""),ROW(CandTable[candidateID])-ROW(CandTable[[#Headers],[candidateID]]))),"")

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-18-2018 06:27 AM

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

Thanks!

Ronna

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-18-2018 07:55 AM

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]]))),""))

=TEXTJOIN(", ",TRUE,IFERROR(INDEX(CandTable[jobOrderID],SMALL(IF(CandTable[candidateID]=$B$3,ROW(CandTable[candidateID]),""),ROW(CandTable[candidateID])-ROW(CandTable[[#Headers],[candidateID]]))),""))

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
24.6K
Views

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
23.2K
Views

10 Likes

13 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
136K
Views

6 Likes

28 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
11K
Views

0 Likes

9 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
23K
Views

0 Likes

7 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft