Cross-Sheet Functions
I am trying to return values to a new sheet from a form sheet if the cells in a particular column of the form sheet contain any data. For instance, if FORM sheet column 4, row 1 contains data, put that data in the NEW sheet, column 1, row 1. If FORM sheet column 4, row 2 does not contain data, then no action. If FORM sheet column 4, row 3 contains data, put that data in NEW sheet, column 1, row 2.
Answers
-
Hi @jacvaughn
Is there a reason you want to do this in another Sheet, versus using a Row Report and adding filters?
If it has to be a sheet, you could use the INDEX(COLLECT combination to bring back data based on your criteria (that the cells "aren't blank"). However you would need to have one column in your current sheet that has the row number listed down it, 1, 2, 3, 4, etc for the formula to read.
Then you could do something like so:
=INDEX(COLLECT({Column with data to bring back}, {Criteria Column 1}, <> "", {Criteria Column 2}, <> ""), [Row Number]@row)
See: Lookup one cell using multiple criteria
Here's another Community post with screen captures: How to Return only nonblank values from column
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Jacvanghn
I hope you are doing well,
As I am assuming this condition is fixed for column 1 row 1 and column 1 row 2 in NEW Sheet. If,
- Column 4, row 1 contains in the FORM sheet so it will update in column 1 row 1 in NEW Sheet.
- Please use cell linking for this condition. Right-click on column 1 row 1 then click on “Link from cell in other sheet”, select FORM Sheet then to get a reference select column 4 row 1 then click on Create link.
- Column 4 row 2 contains in the FORM sheet so it will update in column 1 row 2 and if Column 4 row 2 does not contain, it will be getting a reference from column 4 row 3 from FORM Sheet.
- To achieve this condition, please use this formula
- =IF(ISBLANK({Column4 row2}), {Column4 row3}, {Column4 row2})
If column 4 row 2 has to contain and column 4 row 3 also has to contain in the FORM Sheet, it will be getting data from column 4 row 2.
I hope this is useful to you, please let me any changes are required.
Have a Good Day.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!