Expanding array

Question

I have expanded a table by adding rows . The table is the source for an array. The formula in the destination table has changed to reflect the expanded array but will not populate outside the cells of the old formula. Can anyone help Please?

Answer 1

You should convert your 'table' to an actual Excel Table Object - use Insert Table.

Then you can change your formula to something like

=IF(Table1[Data]="", "", Table1[Data])

Where column D has the header "Data" in cell D1. Then when you add rows to your table, your formula will capture all the values in that column.

If you don't want to do that, the other approach is to insert rows within the referenced range of your formulas - for example, insert a new row with D283 as the active cell, and the formula will expand to include values up to D284 - the value in D283 will move down, but your formulas will work to capture all the data.

Answer 2

Hi Bernie

Thanks for you reply. Silly me!

=IF(Calculate!D1:D283="", "", Calculate!D1:D283)

And =Calculate!AA4:AB283 same results with both formulas.

Answer 3

It would help if you posted the formula that you use....