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.


  • Genevieve P.
    Genevieve P. Employee Admin

    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



  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    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. 


    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!