Range Name for "Offset()" without using "Offset()"

[foxguy]

I want to create a Name that points to the cell 2 cells below $D$18 without using a volatile function or a UDF(). I want to then use that Name in other Names.
I also want it so that if I cut and paste $D$18 into $G$30, the Name will point to $G$32.

I know that "=Offset($D$18,2,0)" points to the correct cell, but I don't want to use a volatile function like Offset() or Indirect().

I started trying to put it in R1C1 notation: ="R"&Row($D$18)+2&"C"&Column($D$18) produces R20C4 but I can't figure out how to put that into a Name so that it points to $D$20.

I can live with it if the Name doesn't include the "$"s.

[Miraun]

... can't you just lose the absolute $ references, and then use whatever cell is 2 below your active cell?

[foxguy]

You lost me.
What would be entered into the "RefersTo" when I'm creating the Name?

[Miraun]

I completely misunderstood the question... I can't figure out any way to accomplish it without using a volatile definition in defining the named range area. Best I came up with was:

=ADDRESS(ROW(Win)+2,COLUMN(Win),1)

But I don't know how to get around the use of an indirect without a UDF. Sorry, guess I'm no help!

[Marcol]

ADDRESS() is also volatile.

Try

Please Login or Register  to view this content.

[foxguy]

Marcol;

That's a whole lot closer to what I want than I've ever been able to come up with. I might be able to adapt it to what I want.

Now I have to figure out how to adapt it for the other end of my dynamic range.
Maybe you have an idea?

What I eventually want is to replace my current Name "=Offset($D$18,2,0,$B$13,1)" with a non volatile Name. So a suggestion on how to point to the cell that is the value of $B$13 below $D$18?

[romperstomper]

It appears to me that your question is really:
How do I refer to a cell indirectly without any of the consequences of an indirect reference? If you can do that, your references won't recalculate.

[foxguy]

RS;

I hadn't thought of it that way. I'm not convinced yet, but you may be right.

It seems that I should be able to define a Name that is only dependent on the value of some cells and location of defined cells, so that the Name only recalculates when the value or location of those cells change.

Marcol came up with a way to indirectly point to a single cell, and I don't think it is volatile (=Index($D$18:$D$20,3,1)). If I can do the same thing with the other end of the dynamic range then I don't believe the Name referring to that dynamic range would be volatile.

Where is my thinking wrong?

[Marcol]

I'm not clear on what you mean by "the other end of my dynamic range".

If the start cell is say

Please Login or Register  to view this content.

And the end cell is

Please Login or Register  to view this content.

Then the dynamic range becomes

Please Login or Register  to view this content.

So if Start and End are the names then this would give the whole range

Please Login or Register  to view this content.

$D$100 could be varied to suit the largest value for $B$13

[romperstomper]

It's not volatile, but it also won't work for your purposes. If you define a name as referring to:
=Index($D$18:$D$20,3,1)
and then move D18 to say K21, the name definition does not alter.

[foxguy]

Marcol;

You're real close to what I'm looking for.
The problem is that the user might cut the range and paste it somewhere else. So unless EndCell points to a cell that gets cut it then crashes.

Right now $B$13 contains 206, but it can go up at any time. It probably will not ever reach 400 so I would want to define EndCell: =INDEX(Sheet1!$D$18:$D$400,Sheet1!$B$13+1,1)
So =StartCell:EndCell would refer to $D$20:$D$225. If the user then cuts $D$18:$D$225 and pastes it into $G$28:$G$235, EndCell would then be =INDEX(Sheet1!$G$28:$D$400,Sheet1!$B$13+1,1) because $D$400 wasn't cut. So the Range =StartCell:EndCell would then refer to $D$30:$D$235, but the data is in $G:$G.

But it's close.

[Marcol]

I don't follow what you are trying to do, why cut and paste?

If you select D18:D20 and cut to K21 the name will point to K23.

[EDIT]
This refers to my first post and romper's' reply.

[foxguy]

RS;

I can safely assume that $D$18 would not be moved by itself. The user would always cut $D$18:$D$???. If a user only cut the heading of the column without the data underneath, then they deserve what they get.
So the Name would move with the cut cells as long as all cells referenced in the Name are cut.

Am I wrong?

[foxguy]

Marcol;

In order for StartCell:EndCell to move with the cut & paste, ALL cells referenced in the Name have to be cut.

If EndCell references $D$400 and $D$400 is not cut then EndCell will refer to a cell that is not in the data that was cut & pasted.

[foxguy]

Marcol;

Forgot to answer why I would cut & paste.
I wouldn't, but other users might. I originally defined the range =Index($D:$D,Row($D$18)+2,1):Index($D:$D,Row($D$18)+207,1) and it worked fine, the a user cut the range $D$18:$D$224 (all the data) and pasted it in $G$30. The Name then was =Index($D:$D,Row($G$30)+2,1):Index($D:$D,Row($G$30)+207,1), but the data was in $G:$G.

[Marcol]

I can see why cut and paste is a problem, if you cut part of the range it will fail, but cutting all of the range will be okay. but why should this happen?

Can't you just protect the sheet or range to avoid this?

[foxguy]

Marcol;

It's not my file. The owner wants to keep the ability to unprotect the sheet and rearrange it. He's already screwed things up by moving data and the Name didn't move with it. I replaced all the Names with Offset() which protected against his moving the data, but there are literally thousands of Names using Offset() and the workbook is too slow for him. So now I'm trying to find a way to get rid of the volatile functions and still allow him to move the data.

[Marcol]

Sounds to me like you're flogging a dead horse here!

What the owner seems to want is a solution that fixes everything when he screws things up!
Why cut and shift part(s) of a table, surely some disciplines have to be maintained?

Come to think about that, an Excel Table/List could be a route to try.

[foxguy]

Marcol;

He doesn't move part of a table. He moves the entire table. But he does rearrange the columns in the table, so I Name each column, that way I don't have to use vLookup().

I don't understand what you mean by "Excel Table/List". What is that?

[romperstomper]

Seems like an awful lot of work just to avoid VLOOKUP.

[foxguy]

RS;

No trouble at all avoiding vLookup(). I was just telling him why I have so many Names. I use Index(ResultsColumn,Match(Value,SearchColumn,0)) instead of vLookup(). I actually do that in all my files. It allows me to rearrange, insert, delete, etc. while I'm developing. It works great. The only downside I've ever discovered is this file that uses Offset() in so many Names.

[Marcol]

See if this workbook gives you some ideas.

It uses an Excel Table/List (See the names manager "Table1")

If this table is moved, has rows added/removed, or the header is changed, then the other names should reflect this.
Similarly if you change the table name.

This is an example name from the workbook

Please Login or Register  to view this content.

In 2007 this is an Excel Table, in 2003 it was known as a List.

In 2007 Select the range to make into a table, then with Tab "Insert" select "Table"

Note
This workbook will not work properly in 2003, the table will have to be recreated as a list.

[foxguy]

Marcol;
Any chance you can save it as an .xls file? I don't have access to 2007 right now.

[Marcol]

I'll have to find and fire up a 2003 machine, and change the table to suit, might be a little while, bear with me.

[foxguy]

Marcol;

Don't worry about it. I'm going to bed in 10 minutes. I'll get on my other computer tomorrow and check it out.

[foxguy]

Marcol;

I finally got around to checking out your file. I don't see how it helps.
I cut $M$6:$M$56 and pasted it in column O. StartCell and EndCell still pointed to $M$8 & $M$56, but the data was no longer in column M.
I have never used a Table like this so maybe I didn't understand how it works, but it didn't allow me to move the data the way I wanted.

[Marcol]

If you move the whole table, M5:M88 in this example, it works.
If you try to move only part of the table the data will be moved but the table remains intact in its' original postition, and because the names refer to the table they also remain where they were.

[foxguy]

Marcol;

That's the problem I'm trying to solve.
The user might move only part of a Range. He will only move that part that is visible with data in it. I don't want to have to train him how to make sure that he's moving the entire Range (because I don't trust him to always remember to do it properly) so I'm trying to make it impossible to move only partial Ranges.
It works fine as long as I use Offset() because the Name only references 1 cell and he can't move the data without moving that 1 cell. But Offset() is volatile and there are so many of them that the speed of the workbook is noticeably slower than it use to be.
So I'm now trying to find a way to reference 2 cells (top and bottom of the Range) in such a way that the data cannot be moved without moving both cells. But I can't figure out a way to reference the bottom cell in the Range. That's a better way of describing my problem. If I start another thread, I think that's how I will describe my problem.

Actually it just now occurred to me that it might be possible if I make sure that Row(65536) is part of the data (just hide 65000+ rows) and have the UsedRange move up instead of adding to the bottom of it. I've never tried anything like that so I'm going to have think long and hard about the side effects.