Haha, I appreciate the update.
In the link Joe provided there were 3 options the 3rd being VBA. I hope you went with the VBA option. I often use a fill down macro that I have stored in my Personal Macro workbook for one off fill down scenarios.
got fed up of having to type the formula every single time.
Just from an spreadsheet technique perspective. Your comment about having to type in the formula each time indicates you are not using the helper column as intended.
Most spreadsheets need to have the data refreshed at regular intervals and this generally means you will get the same number of columns but a different number of rows each time.
You therefore want to set up all calculation columns (including helper columns) 1 column to the right of the number of columns required for the data you are receiving.
eg. source data runs from A:F, then your first calculation column should start at G.
Using those column references as an example ......
- The next time you get new data you simply delete the data in A:F (clear content "do not" delete rows or columns).
- Copy in the new data
- Then just make sure your calculation columns cover all the rows used in Columns A:F. If you use Excel Tables you won't even have to do this part since the Table will copy the formulas down.
For your scenario of filling down information, the above is actually a more automated way than using either the Goto blank cells or Find & Replace methods suggested in the Link.
It is also a much cleaner and efficient spreadsheet design if you
don't want to use VBA or Power Query to add information and to rearange data, and you are currently moving columns manually and reentering formulas each time you refresh the data.