Looking to create a data reference across tabs to determine row value

Melanie Proebstel

We have many columns that need to be reviewed by this formula - within these columns, multiple answers will return the same value. For example: Column A could show Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep. All those are considered Cleanup. However, if we change that drop column to Oct or Nov, that column is now considered Current.

I have 15 columns to look at this way. If all of the columns have answers from the same status "Current" or "Cleanup" it returns an overall judgement of the status of that row overall.

I currently do this in google, but am having other issue with stagnant data, etc, so I'd like to move to Smartsheet, if possible. Any ideas?

This is my current Google Formula for the review column only:

=ARRAYFORMULA(IF((IFERROR(MATCH( Q5, Current, 0)))*(IFERROR(MATCH( R5, Current, 0)))*(IFERROR(MATCH( S5, Current, 0)))*(P5<>"")*(T5<>"")*(IFERROR(MATCH( U5, Current, 0)))*(IFERROR(MATCH( V5, TR_Year, 0)))*(IFERROR(MATCH( W5, TR_Type, 0)))*(IFERROR(MATCH( X5, Current, 0)))*(IFERROR(MATCH( Y5, Current, 0)))*(IFERROR(MATCH( Z5, Current, 0)))*(IFERROR(MATCH( AA5, Current, 0)))*(IFERROR(MATCH( AB5, Current, 0)))*(IFERROR(MATCH( AC5, Current, 0)))*(IFERROR(MATCH( AD5, Current, 0)))*(IFERROR(MATCH( AE5, Current, 0)))*(IFERROR(MATCH( AF5, Current, 0)))*(IFERROR(MATCH( AG5, Current, 0)))*(IFERROR(MATCH( AH5, Current, 0)))*(IFERROR(MATCH( AI5, Current, 0)))*(IFERROR(MATCH( AJ5, Current, 0)))*(IFERROR(MATCH( AK5, Current, 0)))*(AP5<>""), "Current", (IF((IFERROR(MATCH( Q5, Catchup, 0)))*(IFERROR(MATCH( R5, Catchup, 0)))*(IFERROR(MATCH( S5, Catchup, 0)))*(P5<>"")*(T5<>"")*(IFERROR(MATCH( U5, Catchup, 0)))*(IFERROR(MATCH( V5, TR_Year, 0)))*(IFERROR(MATCH( W5, TR_Type, 0)))*(IFERROR(MATCH( X5, Catchup, 0)))*(IFERROR(MATCH( Y5, Catchup, 0)))*(IFERROR(MATCH( Z5, Catchup, 0)))*(IFERROR(MATCH( AA5, Catchup, 0)))*(IFERROR(MATCH( AB5, Catchup, 0)))*(IFERROR(MATCH( AC5, Catchup, 0)))*(IFERROR(MATCH( AD5, Catchup, 0)))*(IFERROR(MATCH( AE5, Catchup, 0)))*(IFERROR(MATCH( AF5, Catchup, 0)))*(IFERROR(MATCH( AG5, Catchup, 0)))*(IFERROR(MATCH( AH5, Catchup, 0)))*(IFERROR(MATCH( AI5, Catchup, 0)))*(IFERROR(MATCH( AJ5, Catchup, 0)))*(IFERROR(MATCH( AK5, Catchup, 0)))*(AP5<>""), "Catchup",(IF((IFERROR(MATCH( Q5, Cleanup, 0)))*(IFERROR(MATCH( R5, Cleanup, 0)))*(IFERROR(MATCH( S5, Cleanup, 0)))*(P5<>"")*(T5<>"")*(IFERROR(MATCH( U5, Cleanup, 0)))*(IFERROR(MATCH( V5, TR_Year, 0)))*(IFERROR(MATCH( W5, TR_Type, 0)))*(IFERROR(MATCH( X5, Cleanup, 0)))*(IFERROR(MATCH( Y5, Cleanup, 0)))*(IFERROR(MATCH( Z5, Cleanup, 0)))*(IFERROR(MATCH( AA5, Cleanup, 0)))*(IFERROR(MATCH( AB5, Cleanup, 0)))*(IFERROR(MATCH( AC5, Cleanup, 0)))*(IFERROR(MATCH( AD5, Cleanup, 0)))*(IFERROR(MATCH( AE5, Cleanup, 0)))*(IFERROR(MATCH( AF5, Cleanup, 0)))*(IFERROR(MATCH( AG5, Cleanup, 0)))*(IFERROR(MATCH( AH5, Cleanup, 0)))*(IFERROR(MATCH( AI5, Cleanup, 0)))*(IFERROR(MATCH( AJ5, Cleanup, 0)))*(IFERROR(MATCH( AK5, Cleanup, 0)))*(AP5<>""), "Cleanup","Cleanup"))))))

Please help me!

Paul Newcome

Can you provide a screenshot with some mock data manually entered that shows what you are working with and what you are trying to accomplish?