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

Yuzna, Eben 1 Reputation point
2022-12-01T17:33:55.05+00:00

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.

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,480 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,639 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Prasad Chava 11 Reputation points
    2022-12-01T17:55:03.703+00:00

    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***

    1 person found this answer helpful.