Cross Sheet VLOOKUP/Index/Match?

Lina Menard

Hello! I have a Project List and a Capacity Planner. I would like to have the Project Name (from the Project List) be automatically added to the Capacity Planner if the Active box is checked on the Project List. I have tried with a VLOOKUP and I'm getting an incorrect argument set error message. Could someone please share the formula I should be using? Thank you!

Devin Lee

Generally it's better to use INDEX/MATCH over VLOOKUP because the column number in VLOOKUP is static so if you add more columns to your sheet it will reference the wrong column sometimes.

Use the formula below with the two references to the other sheet.

=IF([Active box]@row = 1, INDEX({Reference 1}, MATCH([Unique ID]@row, {Reference 2}), 0), "")

Reference 1 = [Project Name]:[Project Name]

Reference 2 = [Unique ID]:[Unique ID]

This is what it would look like if all the columns where on the same sheet and Unique ID 2 was the value you have manually typed in.

=IF([Active box]@row = 1, INDEX([Project Name]:[Project Name], MATCH([Unique ID]@row, [Unique ID]:[Unique ID 2]), 0), "")

Lina Menard

https://community.smartsheet.com/discussion/comment/311870#Comment_311870

Thank you for your response, Devin! 😁 Unfortunately, I wasn't able to get that to work either. I couldn't figure out what "Unique ID" was supposed to be. (We just use project names to identify them). I just added in a new auto number column to try to use that as my Unique ID column. When I use that for reference 2 I'm still getting "unparsable." 😣Any other ideas?

Here's the formula I ended up with: =IF([Active box]@row = 1, INDEX({studioWEBSTER Project List Range 5}, MATCH([Unique ID]@row, {studioWEBSTER Project List Range 6}), 0), "")

Also, I am using a different sheet, so not needing your instructions for the same sheet, but the goal here is to NOT have to type anything in manually. If we have to type something manually we might as well just put the project name on the Capacity Tracker sheet! 🙃I figured there had to be a way for the computer to do it, but now that I've spent far longer bashing my head against the wall trying to figure this out than it would have taken me to type all of them, I'm not convinced. 🤣The goal is to have consistency so that any projects that are active on the Project List also show up on the Capacity Tracker.

ChelseaH

Have you considered using DataMesh? You could create a report that filters the projects with only the Active box checked and then have the DataMesh add the rows to your Capacity Sheet, along with any other pertinent project info. DataMesh works like a VLookup or Index/Match, but much more user-friendly.

Problem with this is that it will only add rows. If rows are removed from the report (project active box becomes unchecked), you will need to manually delete rows from the Capacity Sheet. You could work around this by having the Capacity Sheet automatically move rows elsewhere when a project is completed or no longer active. (Maybe change the Active checkbox to a dropdown menu with statuses: Active, Completed, Closed, etc.?)

DataMesh is an add on to your Smartsheet account, so you may need to contact your sales rep if you don't already have it.

Lina Menard

https://community.smartsheet.com/discussion/comment/312428#Comment_312428

ChelseaH, thank you for your suggestion. I hadn't ever heard of DataMesh. I'll look into the add-on to determine whether it makes sense for us. Thanks for the tip!

ChelseaH

Happy to help!

I've found it to be invaluable with getting around the Smartsheet cell link limits. Worth every penny to make my job easier!