Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 113
Default reference first cell in a named range

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.






  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default reference first cell in a named range

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)

--

Regards
Roger Govier

"Robert H" wrote in message
...
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.






  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default reference first cell in a named range

Just substitute Roger's formula for $B$7

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


--
Biff
Microsoft Excel MVP


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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)

--

Regards
Roger Govier

"Robert H" wrote in message
...
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.








  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 113
Default reference first cell in a named range

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

On Jan 14, 1:59 pm, "T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
automatic range - named range give me circular reference... George Thorogood Excel Discussion (Misc queries) 0 February 22nd 07 08:53 PM
HOW TO EXTRACT CELL REFERENCE AS TEXT FROM NAMED RANGE romelsb Excel Worksheet Functions 0 November 3rd 06 10:49 PM
Named Range reference via single Cell Graham Excel Discussion (Misc queries) 0 July 26th 06 09:37 AM
inserting a named range into new cells based on a named cell Peter S. Excel Discussion (Misc queries) 1 June 4th 06 03:53 AM
named range, offset self-reference George Excel Discussion (Misc queries) 6 November 7th 05 12:21 AM


All times are GMT +1. The time now is 07:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"