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.