Named ranges using a named formula

The attached sample worksheet includes a small table that relates to the issue that I am posting about.

I want to set up the data in each column in a worksheet as a dynamic named range using the OFFSET function.

For example, the name defined for column A (called rngCode) could refer to:

Please Login or Register  to view this content.

One alternative would then be to define a named range for each additional column using the same formula with only some minor cell reference changes/differences.

For example, the name defined for column B (rngColB) could refer to:

Please Login or Register  to view this content.

or, better yet:

Please Login or Register  to view this content.

(Note the height of the named range for column B depends on a count of the entries in column A, not column B).

As another alternative, I thought I could achieve the same thing by placing the cursor at cell A2 and then defining a named formula (called NamedFormula) that refers to:

Please Login or Register  to view this content.

Then use this named formula to create a relative named range for each column. For example, move the cursor to cell B2 and then define a named range for column B that refers to:

Please Login or Register  to view this content.

Then repeat this process by moving the cursor to cell C2 and then define a named range for column C that also refers to:

Please Login or Register  to view this content.

I thought the trick was to make sure that I selected a reference cell before opening the Define Name dialog and setting each new column range.

Unfortunately this doesn't seem to be the right way to go about it, as when I try to select the various ranges using Go To, I get results different to what I expect.

I am now unsure if this is possible or if I am simply going about it the wrong way.

Thanks in advance.

Thanks for your reply Marcol.

No, my issue isn't a lookup issue. It has to do with whether I am able to define a series of relative named ranges based on a named formula.

There are 2 or more ranges that are each defined using an OFFSET formula that is almost identical except for the first argument (the reference argument). I thought I may be able to define a named formula (the OFFSET formula) and then simply use this named formula as the reference for the remaining named ranges.

In the worksheet attached to my initial post:

- the rngCode name should resolve to A2:A7
- the rngColB name should resolve to B2:B7
- the rngColC name should resolve to C2:C7
- and so on for any other columns added beyond column C.

I have defined rngCode as an OFFSET formula referring to column A. I then defined NamedFormula as an OFFSET formula whose height involves a reference to rngCode. The named ranges for columns B and C (rngColB and rngColC) are both defined as =NamedFormula.

It is my understanding that I can define relative named ranges, and am wondering if it is possible to do this by using a named formula as the reference for these relative named ranges.

I have tried playing around with the reference argument in NamedFormula by using $A$2 and A$2, but I can't seem to get the ranges rngColB and rngColC to resolve properly.

Hi Marcol

I had a closer look at the worksheet you attached, in particular how you defined the 2 named ranges.

So just confirming again that I don't believe it is the solution to my problem AFAICT.

Got to go shortly.
I'll look again later in case nobody else helps.

Bump no response