Been trying this for a few hours, trying to fill down column F G H automatically when a new row is added (my data is on another sheet that's coming in from forms)


Any tips or hints how to get it to fill?


To accomplish this you just need to use an arrayformula:

={"Product ID";arrayformula(if(D2:D="","",right(D2:D,4)))}

A word of caution, always delete excess rows in your spreadsheet, in your example you are only using 10 rows of data but your spreadsheet has 1,000 rows, make sure to delete those excess rows so you only have the amount of rows you actually need as using an arrayformula like this will have to calculate every row even though it assigns the rest blank.

| improve this answer | |
  • Took a bit of trial and error but I got it based on your formula. Thanks! – Felix Pau Dec 13 '19 at 15:31
  • @FelixPau it is better to leave {"Product ID";... this way you have your formulas contained in the header row... why put it in row 2. – CodeCamper Dec 13 '19 at 15:46
  • I was playing with it and couldn't get it to populate down, probably didn't clear the cells below to automatically fill. I updated to your suggestion and fixed the other 2 columns as well, the formula is in the header cell and it fills down. Thanks again! – Felix Pau Dec 13 '19 at 16:00
  • @FelixPau Great! Glad it worked, sadly or maybe luckily ArrayFormula does not overwrite data so if you ever happen to type in that column accidentally (even if it is identical data) you just need to delete everything below the header and it will automatically fix itself, which is one of the reasons I suggest always having the formula in the header (it allows you to safely delete everything below it) – CodeCamper Dec 13 '19 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.