Does INDEX(MATCH) work on a WBS column with WBS values generated by a formula?

bmckiss

Hello, I have created a project schedule template for my team, with tasks, subtasks and associated timelines on one sheet. I have a second sheet where I am creating a procurement template to track our project materials. I would like the procurement template to pull the task name and target date from the project schedule by using the INDEX(MATCH) formula with the WBS value as the identifier.

Project Schedule Sheet - WBS value is autogenerated with the following formula:

=IF(NOT([Skip WBS]@row), UPPER(IF(COUNT(ANCESTORS()) = 0, [Level Code]@row, JOIN(COLLECT(ANCESTORS([Level Code]@row), ANCESTORS([Level Code]@row), NOT(ISBLANK(@cell))), ".") + "." + [Level Code]@row)))

Procurement Tracking Sheet:

Task Name formula: =INDEX({Task Name}, MATCH(WBS@row, {WBS}, 0))

Target Date formula: =INDEX({Start Date}, MATCH(WBS@row, {WBS}, 0))

As you can see, if the WBS cell is left blank, the formula works as it is pulling in the Task Name for the blank WBS "value" in the project schedule sheet. However, When I enter an actual value, I get a #NO MATCH result.

I have scoured the community for similar situations as this should be quite straight forward but nothing I try seems to work. Greatly appreciate the help to better understand what I can do to resolve.

Lucas Rayala

EDIT-- PLEASE READ MY ADDITIONAL COMMENTS BELOW.

Hi @bmckiss, I think the problem is that the below formula creates a string, not a number. Basically you are creating words and trying to search for words using numbers, which doesn't work.

=IF(NOT([Skip WBS]@row), UPPER(IF(COUNT(ANCESTORS()) = 0, [Level Code]@row, JOIN(COLLECT(ANCESTORS([Level Code]@row), ANCESTORS([Level Code]@row), NOT(ISBLANK(@cell))), ".") + "." + [Level Code]@row)))

You want to turn that formula output into a number, so you want to wrap it in a VALUE function. You will likely want to in-turn wrap that in an IFERROR to avoid issues. Try this:

=IFERROR(VALUE(IF(NOT([Skip WBS]@row), UPPER(IF(COUNT(ANCESTORS()) = 0, [Level Code]@row, JOIN(COLLECT(ANCESTORS([Level Code]@row), ANCESTORS([Level Code]@row), NOT(ISBLANK(@cell))), ".") + "." + [Level Code]@row)))),"")

Lucas Rayala

However, looking at your WBS column, I think you're going to run into issues because you are going above 1.9 to 1.10, and you can't even type the value "1.10" -- if you try, you will get "1.1".

If you type a single quote in front of 1.10, Smartsheet will recognize this as a string, and it will remain visible. In that case, you need to NOT use the VALUE function around the WBS that I suggested above. However, I'm almost 100% certain that you will not get a match between the typed value of '1.10 and the formula value created, without some finagling.

The question is, will users understand how to add these values? Is it possible to change your WBS numbering sequence?