Index Match/ V look up or Data Mesh?

Jack Parry

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!

Andrée Starå

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!

Jack Parry

@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?

Jack Parry

@Andrée Starå

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))

Jack Parry

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..

Andrée Starå

@Jack Parry

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)

Jack Parry

@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!

Andrée Starå

@Jack Parry

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!

Jack Parry

@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!

Andrée Starå

@Jack Parry

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!