Make formula automatically apply? Count only down to the last row of data?

[thisisbaris]

Hi folks,

1. My spreadsheet contains a mix of raw data (transactions) and formula. I would like the formula to automatically apply as soon as the raw data is pasted in, so I have pre-dragged them down to the 15,000th row ... is there a smarter way of doing this?

2. I made a Summary sheet which counts the number of transactions (by transaction ID, 1 per row) and also counts the number of missing or duplicate fields. However, if in one month there was only 10,000 transactions and therefore 10,000 rows of data have been pasted in, this sheet will still be counting up to the 15,000th row ... so it incorrectly shows that there is 5,000 missing fields and/or 5,000 duplicate fields.

How can I fix this? Can't I make Excel scroll down to the very bottom of the raw data and remove all rows below? Many thanks in advance!

 

[Jeffrey Mahoney]

It sounds like you need to create dynamic named ranges. These named ranges will shrink and grow with your data set. You can use named ranges in formulas instead of raw ranges such as C1:C1000.

This one works on columns of numbers
=OFFSET(Sheet5!$C$1,1,0,MATCH(1E+300,Sheet5!$C:$C)-ROW(Sheet5!$C$1),1)

This one works on columns of text
=OFFSET(Sheet5!$C$1,1,0,MATCH("zzzzzzzzzzzz",Sheet5!$C:$C)-ROW(Sheet5!$C$1),1)

Jeff