How to stop Microsoft sheets from skipping a row with my formula when a new answer is entered on Microsoft forms

0
votes

Got a formula (shown below) that I am trying to apply to Microsoft form answers. Microsoft forms appears to automatically insert a new row with every submitted answer which prevents the formula from working on the correct referenced cell. Is there a formula on Microsoft excel online that will reference correctly when the form inserts a row?

If I manually drag formula down it updates properly and shows correct cell reference. Tried importrange and query to link the data from the form excel sheet to another sheet but these functions aren’t included on excel online. I also tried indirect function but I could not modify my existing formula to work with it.

=IF(RAW!M2<>””,RAW!M2,””) This formula transfers data from “RAW” data sheet to a separate sheet and leaves cell blank if no entry is found in the source cell.

1
excelformula

1 Answers

0
votes

If inserting rows breaks references, you can use Index with a row number.

Instead of

=IF(RAW!M2<>"",RAW!M2,"")

use

=IF(Index(RAW!M:M,row(A2)<>"",Index(RAW!M:M,row(A2),"")

When copied down, the reference to A2 will change from A2 to A3 and so on and the returned value will change accordingly. The formula will always return the value from row 2 if the formula references Row(A2), regardless of rows being inserted or deleted.