INDEX and MATCH across two sheets: a detailed explanation
Comments
-
@Mariann Carmen If you already have the multiple resource managers in a single cell and you want to bring that single cell with multiple RMs over into a single cell, then all you should need to do is make sure both columns are formatted to allow multiple contacts in a single cell.
If you are wanting to JOIN multiple contact cells together into a single multi-contact cell, that is not going to be possible with a formula.
-
Hi @Paul Newcome!
Yes, I would like to JOIN multiple contact cells together into a single multi-contact cell. I tried the first suggestion, making the columns formatted to allow multiple contacts, but that didn't work.
Then I tried the following formula: =JOIN(COLLECT([Resource Manager]:[Resource Manager], [Assigned To]:[Assigned To], [Assigned To]:[Assigned To] = true), ", ") . This works well when the Assigned To column is a checkbox, and it collects the Resource Managers into one box the way I need. However, in my real scenario, I don't know the Assigned To resources until they are entered into the column (not as a checkbox), and that is what needs to drive the collection of their Resource Manager from the worksheet. In my worksheet, I have one Resource for one Resource Manager (so one Resource Manager could have many Resources assigned to them, all on separate rows in the worksheet).
Any suggestions on how to do this without the checkbox, triggered when the Resource is added?
-
@Mariann Carmen That goes back to my last comment.
If you are wanting to JOIN multiple contact cells together into a single multi-contact cell, that is not going to be possible with a formula.
-
@Paul Newcome, what formula should I use to accomplish this? I'm stumped.
-
@Mariann Carmen There is no formula that can make this happen.
-
Ok. Thank you.
-
@Andrée Starå, No, unfortunately that didn't work. I'm told that there isn't a formula that can accommodate this.
-
Hey @Mariann Carmen
Please feel free to vote on and add your voice to this Product Idea: Combine Contacts from multiple columns into a single cell
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hello, I have been following the instructions listed here but can't seem to figure out where I'm going wrong in my formula. I am trying to create a public facing Smartsheet where users can look up a breakdown of their total billing for an event by looking up their event number. I was able to make this work when all of the data was on the same page, but when I tried to build in references to another page, my formula became unparseable.
Here is what the formula looked like when it was all in one sheet and worked:
=INDEX([Total Personnel]:[Total Personnel], MATCH(Search@row, [Event #]:[Event #], 0))
And here is what the unparseable formula looks like:
=INDEX({Total Personnel Reference}:{Total Personnel Reference}, MATCH(Search@row, {Event # Reference}:{Event # Reference}, 0))
I built in the references to my other sheet, but they do not seem to be taking. I have tried different positioning for the brackets, but that does not seem to work either.
If anyone has any suggestions, please let me know!
-
@daly.166 When using cross sheet references, you only enter the reference one.
Same sheet:
[Column Name]:[Column Name]
Cross Sheet:
{Column Name}
-
Thank you, Paul! That worked!
-
I have seen your comment in a few different threads: "Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet."
Can you send me a link for info on this helper sheet method? Does it allow you to pull attachments/comments from the source sheet to the destination sheet?
-
@MTodd It does not allow for attachments or comments to be pulled as formulas cannot grab attachments or comments.
-
Hi @MTodd
I hope you're well and safe!
Unfortunately, having attachments linked using formulas is impossible now, but it's an excellent idea!
Possible workaround(s)
- There are ways to have the comments collected and shown in another sheet.
Do you still want my method even if you can't have the attachments pulled to the new sheet?
I hope that helps!
Be safe, and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Can I search for an INDEX match ,with an OR criterion? How would that statement be set up.....struggling a bit with the code: for example
=if(index({Sheet A}, match([SheetBQueryColumnName]@row, {Sheet A}, 0)) = [SheetBQueryColumnName]@row, 1, 0) OR if(index({Sheet A}, match([SheetCQueryColumnName]@row, {Sheet A}, 0)) = [SheetCQueryColumnName]@row, 1, 0)
Is this even allowed?
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives