reference first cell in a named range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert H - 14 Jan 2008 18:15 GMT

I am using the offset function, in the following formula:
=OFFSET($B$7,,COUNTA(Dates))
I want to use the Named Range, "Dates" I to establish the reference
cell "$B$7" rather than actuality entering the reference cell, which
seems kind of redundant to me.

Dates = $B$7-$B$11

Is this possible? Ive tried a few things which resulted in errors or
no data.

Reply to this Message

Roger Govier - 14 Jan 2008 18:28 GMT

Hi
Not sure if I understand what you are after.

To refer to the first cell in a named range
=INDEX(Dates,1)

You could use $B$11-INDEX(Dates,1) or
=INDEX(Dates,4)-INDEX(Dates,1)

Signature

Regards
Roger Govier

> I am using the offset function, in the following formula:
> =OFFSET($B$7,,COUNTA(Dates))
[quoted text clipped - 6 lines]
> Is this possible? Ive tried a few things which resulted in errors or
> no data.

Reply to this Message

T. Valko - 14 Jan 2008 18:59 GMT

Just substitute Roger's formula for $B$7

=OFFSET(INDEX(dates,1),,COUNTA(dates))

Signature

Biff
Microsoft Excel MVP

> Hi
> Not sure if I understand what you are after.
[quoted text clipped - 15 lines]
>> Is this possible? Ive tried a few things which resulted in errors or
>> no data.

Reply to this Message

Robert H - 14 Jan 2008 19:53 GMT

Thats perfect, I knew it wasn't as hard as I was making it.
Thanks to you both

> =OFFSET(INDEX(dates,1),,COUNTA(dates))
>
> --
> Biff
> Microsoft Excel MVP

Reply to this Message