I have a google sheet, Names, populated by a google form. In another sheet, copiedNames, I am referencing Names column B starting from row 100 ='Names'!B100:B and have drop filled this formula into the all the cells below. The row numbers in copiedNames are not the same as the row numbers in Names.
When a new google form entry is input into Names, the formula in copiedNames break. I understand that this is because new rows are being inserted into Names and copiedNames is referencing rows after the inserted rows. How do I reference the inserted rows so that I can keep referencing the new names as they come in?
I've seen suggestions to try arrayformula, index, indirect, but I can't figure out something that works.
