Thanks:  0
Likes:  0

1. ## Dynamic Named Range

Dear all

May i know if it is possible to create dynamic named range with multi columns?

Code:
`=OFFSET(\$A\$1,0,0,COUNT(\$A:\$A),1)`
Above formula is a dynamic named range which i get from microsoft.

I would like to a dynamic named range from A1:AN ......

Thank you very much

2. ## Re: Dynamic Named Range

What kind of data do you have in each column?

3. ## Re: Dynamic Named Range

What kind of data do you have in each column?
I got A = No. B = product number. C and D = product detail. E = name of sale person or blank. F = selling rate. G = VAT. H = buy rate. I = VAT. J to AM are name of ppl I keep commission for. AN = profit/lost. J to AM sometime some are blank

4. ## Re: Dynamic Named Range

Originally Posted by Vanda_a
I got A = No. B = product number. C and D = product detail. E = name of sale person or blank. F = selling rate. G = VAT. H = buy rate. I = VAT. J to AM are name of ppl I keep commission for. AN = profit/lost. J to AM sometime some are blank
Column A is numeric, right?

5. ## Re: Dynamic Named Range

Column A is numeric, right?
Yes

6. ## Re: Dynamic Named Range

Originally Posted by Vanda_a
Yes
Activate Formulas | Name Manager.

Define Lrow as referring to:

=MATCH(9.99999999999999E+307,Sheet1!\$A:\$A)

Adjust the sheet name to suit. If you are on a non-American system, change decimal dot to comma; the list separator to semi-colon: that is:

=MATCH(9,99999999999999E+307;Sheet1!\$A:\$A)

Define DATA as referring to:

=Sheet1!\$A\$1:INDEX(Sheet1!\$N:\$N,Lrow)

Adjust the start cell, that is, Sheet1!\$A\$1, as needed.

If you would like to have PRODUCT separately next to DATA, then define it as referring to:

Either...

=INDEX(DATA,0,2)

Or...

=Sheet1!\$B\$1:INDEX(Sheet1!\$B:\$B,Lrow)

Follow the same logic for other parts as needed/required.

7. ## Re: Dynamic Named Range

It works perfectly. Thank you very much

How the formula works if the column A is not numeric or it is text?

8. ## Re: Dynamic Named Range

Originally Posted by Vanda_a
It works perfectly. Thank you very much

How the formula works if the column A is not numeric or it is text?
When column A is text, you can change the definition of Lrow to:

=MATCH(REPT("z",255),Sheet1!\$A:\$A)

The idea is to pick out the most complete, preferably numeric, reference. Note that the MATCH expressions are faster than COUNTA and they don't miss any in-between empty cell while the latter does.

9. ## Re: Dynamic Named Range

=MATCH(REPT("z",255),Sheet1!\$A:\$A)
Tested it. It works well. Thank you very much

Is it possible if column A is number and text mix up? :D

10. ## Re: Dynamic Named Range

Originally Posted by Vanda_a
Tested it. It works well. Thank you very much
You are welcome.

Is it possible if column A is number and text mix up? :D
Define LrowNum as referring to:

=MATCH(9.99999999999999E+307,Sheet1!\$A:\$A)

and LrowText as referring to:

=MATCH(REPT("z",255),Sheet1!\$A:\$A)

And, finally Lrow as referring to:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},LrowTex,LrowNum,MAX(LrowNum,LrowText)))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•