Auto Sense how much to reference in a column

[Mstg007]

I have been using the (Fill / Down) on Column A with a formula.
It usually goes all the way down to the end of the row. Once I
save my file its close to 20MB from the usualy 4MB.

I usually import data into Column A, but usually do not specify
where the import data ends.
Instead of referencing a all the cells in Column A. Is there something
that can sense the end of the values imported and reference just all cells
from the beginging to the last vaules in Column A?

 

[gorem]

I'm not certain if you mean you need the import to recognize the last row in column A or if you need formulas to do so. If the latter, then I think table-formatting/dynamic ranges are your best bet. Instead of a SUMIF looking at the entire column, for instance, the formula could just look at teh range of rows comprising "Table Column XX," which would end at the last contiguous row in the data table.

 

[Mstg007]

I'm not certain if you mean you need the import to recognize the last row in column A or if you need formulas to do so. If the latter, then I think table-formatting/dynamic ranges are your best bet. Instead of a SUMIF looking at the entire column, for instance, the formula could just look at teh range of rows comprising "Table Column XX," which would end at the last contiguous row in the data table.

I am sorry, but I am not following. Could you give an simple example?

 

[gorem]

Sure - hope this helps. I re-read your initial post and it sounds like you want a formula in Column A to automatically extend to the bottom of your dataset, regardless of how much data is imported. Is that right?

If that is the case, you can convert your dataset to a table (Home tab > Format as table, with Office 2010 and 2013). Now, if you enter a formula in cell A2, that formula will automatically populate every row in your table. Also, when you add new rows to the table, the formula will automatically extend down as far as your new data does. Does that make sense? This and the related links give a pretty good explanation: Using formulas in Excel tables - Excel - Office.com

All this means that you won't ever need to manually fill your formulas to the bottom of your data.

 

[Mstg007]

Sure - hope this helps. I re-read your initial post and it sounds like you want a formula in Column A to automatically extend to the bottom of your dataset, regardless of how much data is imported. Is that right?

If that is the case, you can convert your dataset to a table (Home tab > Format as table, with Office 2010 and 2013). Now, if you enter a formula in cell A2, that formula will automatically populate every row in your table. Also, when you add new rows to the table, the formula will automatically extend down as far as your new data does. Does that make sense? This and the related links give a pretty good explanation: Using formulas in Excel tables - Excel - Office.com

All this means that you won't ever need to manually fill your formulas to the bottom of your data.

That is one way to do it.

Will that still make my file large?
My initial thought was. If i imported stuff in and it went down to from A1 to A20.
It would just select from A1 to A20. If I imported stuff in from A1 to A20122.
It would just select from At to A20122.
(didnt know if that would cut down on the file size.)