Cross-Sheet Functions

jacvaughn

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.

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

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.