How do I get these circles in one sheet get reflected correctly from the Smarsheet project plan?

Bhavya1322

I have created a sheet which helps me track the stages of project and shows the status from the project plan. These circles should be driving up from the project plan with those specific rows, but somehow it is not working and I always have to overwrite the circles with the actual status.

The cells has this formula:

=IF(NOT(ISBLANK([Solution Approved Overwrite]@row)), [Solution Approved Overwrite]@row, IF(VLOOKUP("Solution Approved", {LMHEALTH}, 12, false) = "In Progress", "Green", IF(VLOOKUP("Solution Approved", {LMHEALTH}, 12, false) = "Late", "Yellow", IF(VLOOKUP("Solution Approved", {LMHEALTH}, 12, false) = "Complete", "Blue", IF(VLOOKUP("Solution Approved", {LMHEALTH}, 12, false) = "Not Started", UNICHAR(128280), "ASSISTANCE HERE")))))

Genevieve P.

Hey @Bhavya1322

When you say that it's not working, is it returning a blank cell or an error?

One thing I see is that your VLOOKUP is looking over 12 columns, but you only need the first column and the last column. We can reduce the processing needed by replacing your VLOOKUP with an INDEX(MATCH combination.

This will only look at the 1st and 12th columns as individual references, meaning you can cut out 10 columns of data that's being processed 4x in your formula. It also means that if your source sheet rearranges columns your formula won't break.

For example, this:

VLOOKUP("Solution Approved", {LMHEALTH}, 12, false)

would be this:

INDEX({Status Column}, MATCH("Solution Approved", {Column Reference 1}, 0))

See:

https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations?_ga=2.207788690.101738377.1695725617-686803770.1684510742&_gl=1*fcof9i*_ga*Njg2ODAzNzcwLjE2ODQ1MTA3NDI.*_ga_ZYH7XNXMZK*MTY5NTg5NDMyMS45Mi4xLjE2OTU4OTg0NzcuNjAuMC4w

Try this:

=IF([Solution Approved Overwrite]@row <> "", [Solution Approved Overwrite]@row, IF(INDEX({Status Column}, MATCH("Solution Approved", {Column Reference 1}, 0)) = "In Progress", "Green", IF(INDEX({Status Column}, MATCH("Solution Approved", {Column Reference 1}, 0)) = "Late", "Yellow", IF(INDEX({Status Column}, MATCH("Solution Approved", {Column Reference 1}, 0)) = "Complete", "Blue", IF(INDEX({Status Column}, MATCH("Solution Approved", {Column Reference 1}, 0)) = "Not Started", UNICHAR(128280), "ASSISTANCE HERE")))))

If this isn't working, it would be helpful to see a screen capture of the source sheet to make sure the values we're looking for ("Solution Approved") is spelled the exact same.

Cheers,

Genevieve