Looking to create a data reference across tabs to determine row value
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!
Answers
-
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?
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives