On Friday, June 22, 2012 7:12:55 AM UTC+8, Ron Rosenfeld wrote:
On Thu, 21 Jun 2012 04:59:42 -0700 (PDT), Reppy wrote:

I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have the formula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go.

If your entries are the only thing in column A, and if there are no intervening blank cells, you could use a dynamic name (one that adjusts depending on the extent of the data).

For example: Define Name
Name: Rng
Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A))

If your data is not like that, let me know, and we can come up with something else.

Hello Ron

Just wanted to let you know that this formula also works well. As it is somewhat shorter than the previous ones i might use this one. It is quite amazing he we arrive at the same answer with varying formulas.

Thanks again

Reppy