Hi Andy,

My guess is that you have "predefined" formula in a normal range of cells, waiting for users to fill the data input columns. This way of having preformatted empty rows is not the best idea.

An alternative is to use defined tables. This way, you will not need to have empty rows, whenever users type data under the last row of the table, the table autoexpands. More, if you have columns with formulas, these will automatically extend to the new rows.

Here is a link: https://www.myonlinetraininghub.com/excel-tables

Thanks - that makes sense for the source data, but would the analysis table simply add a row when a row was added to the source data?

No, there is no relationship between tables, a table cannot detect a change in another table.

Depends on what that analysis does, but I'm sure that it can be changed to a more flexible way. For the moment seems like a very rigid structure.

You can use power query to load and process the source data table, then drop the results in the analysis sheet, each time you refresh the query, it will be updated with the new data.

Thanks for the reply which makes sense but I'm still having a dilema on how to structure the data properly.

My original thoughts were to set up an analysis sheet to pull through from a source sheet the info I wanted, then run a pivot table on this analysis sheet.  However I'm thinking that maybe an issue now as the source sheet is constantly having datat added and I want that data to come through imediately to the analysis sheet - thats why I had both sheets set up with blank table rows so the info would simply flow between sheets using VLOOKUP & INDEX / MATCH.  The problem I'm encountering is as you've indicated above, the table stucture shouldn't really have blank rows in either sheet.

So, here is where I'm at in my thinking...

Option: to do away with the analysis sheet all together and run pivot tables in a separate worksbook which point to the source sheet.  The problem with this is that the source sheet is in contant use by other users and contains more info than needed.  However it does mean that I can hold the datat in one place.  I've tried to resize my table to hold no blank rows but the issue is that when I create a new row, all the formulas don't copy down and formatting doesn't work (no idea why as some do).  I have hidden columns and these also don't copy down correctly)

Option: keep the analysis data sheet spearate but manually add data from the source sheet to the end of my table so that the table has no blank rows and my pivot info works - however this does seem quite a labour intensive solution based on how often the source data is changed.  I'm ultinately trying to get to a real-time dashboard solution so manual intervention isn't really an option.

I'm keen to get my data structure correct before I go too far down the line and I'm struggling to come up with the right route to go down.  What I'm trying to do does seem a reasonably obvious thing to do so there must be a soltuion out there.

Hope that makes sense.

If the formula is not added to the new rows, it means that there are multiple formulas or static values in those columns.

You should delete the content of those columns, and type the formulas again, using structured references: instead of using B12 to indicate a cell from row 12 column B, you should use [@[ColumnName]]

"@" means current row, excel will know how to deal with it. Once you have the same formula in the entire column, it will be added to new rows.

Thanks Catalin for your help

Your expnation makes sense as thats how I understood it worked - however something different is happening with my data.

I have a cell with a starting number in it (outside of the table) and in Column B my formula references this cell +1 (e.g. =$B5+1, =$B6+1, =$B7+1) and this formula runs down the column referencing the cell above each time so I get sequential numbers.  This column only contains this formula throughout.  I see you mention structured references and this may be the issue with this column as a new row produces a blank cell in column B.  How do you use structured references in this case?

In column A I use this formula..

=IF(ISERROR((HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH($B5, (TenderCalcTable[Tender No1]), 0), 1), $B5))),$B5,(HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH($B5,(TenderCalcTable[Tender No1]), 0), 1), $B5)))

which returns me a hyperlinked number taken from column B (it hyperlinks to a dirfferent cell on a different worksheet).  Because column B is returning a blank column a returns a hyperlinked 0.  The formatting is wrong as well as the font, colur etc doesnt carry through.

In other columns I have formulas that work but some of the cells have values not formulas (the spreadsheet is large so every so often I change the formulas to values for older legacy data to speed things up as the sheet can run slow).  The fact the formulas work when there are values in the column goes against how you suggest this should work.

Any thoughts?

First formula looks like an index column.

You can use:

=ROW()-1

This assumes that the table header is in row 1. If the headers are in the second row, adjust the formula with: =ROW()-2

This way you will have the same formula in the entire column, it will be introduced automatically in the new rows added.

Instea of B5, you can use structured references:

=IF(ISERROR((HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH([@[ColumnBame]], (TenderCalcTable[Tender No1]), 0), 1), [@[ColumnBame]]))),[@[ColumnBame]],(HYPERLINK("#'Tender Calc'!" & ADDRESS(MATCH([@[ColumnBame]],(TenderCalcTable[Tender No1]), 0), 1), [@[ColumnBame]])))

It will adjust to current row context with that @ operator, (without @ it will refer to the entire column, not just the current row) and the formula will be the same in the entire column.

To replace the formulas, you have to clear first the entire column, only then apply the new formula.

For the column where you have mixed data types, formulas and static values, this will break the formula autocomplete functionality, not much to do.

You can do the processing inside power query, this will remove any calculation load and speed up the file, but data will need to be updated via Table- Refresh to take into account changes in source values.