Every time a new row of answers is added the formula on another shit will skip the already referenced rows where the new data has just been added.
If i manually drag it down across a number of fields it updates properly and shows the correct number. However if i already have it in those fields when an answer from my google form is entered instead of it updating to show the correct number it stays as 'False' , as it would do when referencing a blank field, and the field reference goes up by one skipping the field it was already referencing.
I've tried absolute referencing with "$b2" and moved it to different positions however it still skips the row it was meant to be referencing. Any idea on how to stop this. Code is below-
=IF('Form Responses 1'!B$16 = "dog","1",IF('Form Responses 1'!B$16="Cat","2",IF('Form Responses 1'!B$16="Frog","3",IF('Form Responses 1'!B$16="Bird","4"))))
Essentially there are three columns, Timestamp, Pick a word and email. Those are populated automatically by the google form in to the sheet "Form Responses 1". The column i'm working with is B which is Pick a word. If that cell equals a certain word the corresponding number is outputted in the field with the formula in it. When new data is entered in to B16 for example on Form Responses the formula on the other sheet will change B16 to B17 and skip that row.
Any help is appreciated and I've got a fair understanding of the formulas so don't worry about going in to too much detail :)
Just answered a similar question here. The workaround is to use INDIRECT() with ROW()
=IF(INDIRECT("'Form Responses 1'!B"&Row(A16)) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Cat","2",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Frog","3",IF(INDIRECT("'Form Responses 1'!B"&Row(A16))="Bird","4"))))
Or
=IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X) = "dog","1",IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X)="Cat","2",IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X)="Frog","3",IF(INDIRECT("'Form Responses 1'!B"&Row(A1)+X)="Bird","4"))))
Where X is your offset.
Hope this helps