using dynamic named range

[tmr064]

With a base of Win/XP Prof. and Excel 2002, I entered some test data in A8 thru F15, did an insert/name/define, used a name of "testrange" (w/o quotes), and entered the following formula;

=OFFSET(Sheet1!$A$8,0,0,COUNTA(Sheet1!$A:$A),6)

in the refers to field. My questions are;

1. why doesn't this name show up in the named range field, the field to the left of the formula bar?

2. when I am entering the above formula and try to edit by moving the cursor with the arrow keys, Excel starts plugging in additions to the formula. Is this a bug or MS feature?

3. if I copy that formula and paste it in a cell, it displays as #VALUE. Shouldn't it show a range?

 

[TonyJollans]

hi tmr064,

1. Named Range is a little bit of a misnomer; what you have is, more accurately, a named formula. You can still enter it in the Name box but it doesn't show and doesn't get remembered.

2. A feature - Microsoft trying to help again! If you press F2 when your cursor is in the box you will switch into 'normal' edit mode.

3. This one is more awkward but basically it doesn't make sense stand-alone in a cell, being a reference to a multi-cell range. That said, I can't fully explain the behaviour of OFFSET functions in cells.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SkipVought]

tmr,

You will NOT see the name in the Name Box. Why? I don't know!

However, it DOES appear in the F3 list of available Named Ranges or as Tony has correctly stated Named Formulas.

Using the direction keys in the RefersTo textbox act as direction keys when entering any formula in a cell. You must highlight, delete and replace or use the [BACKSPACE] key.

You can NOT reference a RANGE NAME directly in a cell. You CAN use the RANGE NAME in a formula where you might use any other range like in MATCH, INDEX or VLOOKUP. The EXCEPTION is that if the NAMED RANGE is a SINGLE COLUMN reference, =MyRangeName will return the value from that column corresponding to the ROW in which the =MyRangeName resides.

Hope this helps


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

BTW, faq68-1331 How can I rename a table as it changes size.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tmr064]

Thanks, Tony and Skip. Skip, I had already used that FAQ. It was what prompted my questions. As far as entering the range name in a cell, I was just trying to verify that I had done it right. Any suggestions for that?

Thanks
Tom

 

[KenWright]

Use the range name in conjunction with other functions, eg assuming you have named that formula as MyRange

=ROWS(MyRange) will give you the last row of that range (Assumes your data starts in A1 and has no blanks in it from start to finish)

=ROW(MyRange) will give you the first row of your range

=ROWS(MyRange)-ROW(MyRange)+1 will give you the total number of rows within your range

=SUM(MyRange) will total the values in your range

etc etc

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[tmr064]

Thanks, Ken, =rows() and =columns() gave me what I needed.

Tom

 

[xlbo]

An easy way to check:

After entering the name and formula and clicking the Add button to confirm the entry of the name, left click ONCE in the formula for the range. You should now be taken to the area that the range covers. the extremities of the range should be shown by the "marching ants" - that's how I do a quick check to make sure the range is calculating correctly anyway....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rbwheat]

One more question regarding named ranges:

How do I reference a named range from another spreadsheet file?

Thanks,

 

[SkipVought]

Easiest way to SEE what it is...

Have the 2 workbooks open

=

Window - select the file/sheet - F3 select the Named range - [Enter]



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[johnturgoose]

Slightly more complicated but I believe leads on from the above questions.

I have a dynamic named range CENTRES defined using the OFFSET function in workbook1

in workbook2 i want to paste a link to that range. However, obviously the CENTRES range is growing.

The simple solution is to paste a link to the cells containing the range and all those it will contain during the life of this particular spreadsheet. It will work but I kind of find it untidy to link to 2500 empty cells and obviously it will slow things down. Is there any way of pasting dynamically?

many thanks for any assistance

john

 

[SkipVought]

John,

Please post your question in a NEW THREAD.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]