Excel reference an entire named range

The Sandbox Software/Technology
1

In Excel, I have a named range, KOODI.

KOODI refers to cells A1:A5 in a sheet called SQL.

I want to use the contents of cells A1:A5 in other places within this sheet (perhaps in other sheets in the workbook as well…assume it’s appropriately defined so that the Scope includes the entire Workbook). E.g., in cells A201:A205 I would like to link to cells A1:A5.

I could just do that with cell references, but I’m wondering if there’s a way for me to do that by using the named range, KOODI.

Is this a stupid idea?

If I should ever embiggen KOODI by inserting a few rows it probably won’t automatically update where it’s referenced in A201:A205, e.g.

Yeah, this is probably a stupid idea. I’m still curious if it’s doable.

Thanks,
'###

2

Can use INDEX, but if you enlarge KOODI, the reference number will change. But wouldn’t you WANT to enlarge the other areas of the file that KOODI is being used?

2 Likes
3

Ooh…that’s a good idea…I didn’t think of that.

Yes, I would have to…and I’d have to remember to do that, but, I could just document that…and I’d have to remember where all those places are…I suppose I could search for them.

Heck, this just might work.

Thanks a bunch!
'###

4

I think that if you select a “destination” that is the same shape as the Named Range, type “= <Named_Range>”, then hit Ctrl+Shift+Enter; that should make it an array reference.

However, if Named_Range changes shape, you might have to update all the other references, too.

1 Like
5

One way I have been trying to make references more robust is to use Index(< namedrange >, match(< field >,< rowIDlist >,0),match(< field2 >,< headerIDlist >,0)). If I make sure the headerIDs/rowIDs line up to the actual stuff in , then i don’t care which row number/column number it is, and embiggening becomes perfectly cromulent.

And don’t forget trace dependents. Not an ideal way if there are tons, but at least somewhat helpful.

Array functions have gotten a lot more complicated in newer Excel versions. I haven’t needed to use them extensively, so I am not sure what they can do.

2 Likes
6

I get around that by (almost) always dynamically defining ranges. That way, if I insert rows/columns they get picked up without me having to go back and specify it.

Might add a wrinkle to what 1695814 is asking, but then again I’ve never tried (needed) to do what they’re doing.