Trying to make a master Excel the will auto extend formulas to match rows?

Question

Hey all,

I'm trying to make a master excel to help some departments with equipment reports. My goal was for the user to be able to power query in their complied info and have the formulas I use to extrapolate data auto extend to match the length of their data. Simplified to if user enters 156 rows of data, I want my formulas to auto extend to all 156 rows, so the user doesn't have to manually make them match.

Answer 1

There are multiple ways to do it.

1) Use a VBA macro to set the formula : Initially select the basic range (may be the first row by default) for the excel sheet and in the worksheet_change event, check the filledup cells and keep extending the formula to the next cell or rows. This will have performance impact. But could be effective.
2) Convert the sheet range (rows and columns) to a table : If you have the rows and columns defined, select the range and convert it to table (Insert menu table and select the range). Once you have the formula added to the table, the subsequent rows will have the same formula copied .

*please don't forget to ***upvote* and Accept as answer if the reply is helpful***