Looking for a savvy way to apply formulas to a dynamic # of rows

[dgroshan18]

For my job I have created a fairly complex workbook/template littered with formulas. The basic idea is I have a variety of inputs (internal company queries, data dumps from external partners) which I dump into the template I have created. Whenever I use this template the size of the inputs can range from as little as a few thousand lines to as much as well over a hundred thousand lines. Until now I've just had the formats saved in the first 10 rows or so of each sheet with the rest of the sheet being blank, and will flash fill the formulas each time based on the size of the data inputs. However, I've been asked poka-yoke this template as much as possible so I got to wondering if there's a savvy way to make it so that I can just dump in my data, and the formulas will basically auto-populate to align exactly with the count of rows I need to fill, if that makes sense. Right now the risk is that the user could forget to copy the formulas all the way down and so some of the data will not be analyzed.

Thoughts? Thanks in advance.

[Pete_UK]

You could apply a named range to each of the columns you use, and these names could be dynamic so that they will adjust to the number of rows that you have.

Then your formulae could refer to the named ranges as appropriate, rather than to specific range references.

Hope this helps.

Pete

[shg]

Use a table. The formulas will automatically extend down as data is entered.