address elements in a range in a formula

**smileyc** · 03-01-2009, 04:17 PM

Hi, me again! I have a named range, called 'event' relating to cells a2-a200, I have a named cell, 'eventcounter'. I was hoping that I would be able to use the named cell eventcounter in formulae on the cells in the event range rather than having to use the cell address. So instead of having a formula that was something like this, =if(a3= etc etc.... I could refer to the range event and eventcounter. Eventcouter gets its value simply by counting the number of non blank cells in the event range, so consequently - and here please feel free to smile - I can only figure out how to address the last entry in the event range in the manner that i wish. As always, all and any help is much appreciated. Many thanks.

**shg** · 03-01-2009, 04:37 PM

If there are no interspersed blanks, why not just make the named range dynamic?

Insert > Name > Define event Refers to =A2:INDEX(A2:A200, eventcounter)