# Dynamic Named Range

#### Vanda_a

##### Well-known Member
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
What kind of data do you have in each column?

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

Last edited:
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?

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.

It works perfectly. Thank you very much

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

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.

=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

Last edited:
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)))

Replies
0
Views
258
Replies
15
Views
204
Replies
1
Views
85
Replies
3
Views
125
Replies
0
Views
40

1,202,984
Messages
6,052,913
Members
444,612
Latest member
FajnaAli

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back