Index Match/ V look up or Data Mesh?
Hi, I'm using Smartsheets to input all associated software release notes across many different software versions (each software version has a separate Smartsheet).
My issue is that sometimes certain tasks IDs appear in multiple versions of software. e.g. Task ID: 1234 may appear in software version V10 and V11.
I want to be able to know if a certain task ID appears in different versions across multiple sheets (I have about 20+ sheets) and I'll add some conditional formatting if it comes back as yes..
Would it be best to use Index Match or V Look up? Although I'm not sure if either would work across multiple sheets?
Or would Data Mesh be a viable option?
Thanks in advance!
Answers
-
Hi @Jack Parry
I hope you're well and safe!
I'd recommend a COUNTIFS or INDEX/MATCH formula for each sheet.
Make sense?
I hope that helps!
Be safe, and have a fantastic week!
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.
-
@Andrée Starå how would that look? the task IDs are different on every sheet with the exception of the task IDs I'm trying to locate.
If I have 3 sheets named 'V10' 'V11' V12'
Don't I need to put a value I'm looking for in a COUNTIF formula?
-
I've used the below formula and it works well. I just want to expand it across my multiple sheets:
=INDEX({B16.P21}, MATCH([Function/Task ID]@row, {B16.P21}, 0))
I'm just not sure where to start..
Would it be
=INDEX ({B16.P21},{B16.P20},{B16.P19}, MATCH([Function/Task ID]@row, {B16.P21},{B16.P20},{B16.P19}, 0))
-
Or something like this:
=IFERROR(IFERROR(INDEX({B16.P21 QF}, MATCH([Function/Task ID]@row, {B16.P21 ID}, 0)), INDEX({B16.P20 QF}, MATCH([Function/Task ID]@row, {B16.P20 ID}, 0))))
Although I'm not sure what would happen if the Task ID was present on more than one sheet..
-
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
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.
-
@Andrée Starå now shared! Thank you!
The plan is to include all previous releases for the current version - P22 e.g. P21, P20.. P1
P21 would then look for all its previous releases e.g. P20, P19.. P1
Thanks!
-
I've added the following formula to your sheet.
=IF(COUNTIFS({B16.P21 ID}, [Function/Task ID]@row) > 0, "P21") + " " + IF(COUNTIFS({B16.P22 ID}, [Function/Task ID]@row) > 0, "P22")
Did it work as expected?
✅Remember! 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.
-
@Andrée Starå not quite working how I expect it to.
I agree, need to go back to the drawing board I think. Thanks for all your help!
-
You're more than welcome!
Did you see the email I sent as well? I described another option.
✅Remember! 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 60 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 40 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!