Dynamic Named Range

[Vanda_a]

Dear all

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

=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

 

[Aladin Akyurek]

What kind of data do you have in each column?

 

[Vanda_a]

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

 

[Aladin Akyurek]

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?

 

[Vanda_a]

Column A is numeric, right?

Yes

 

[Aladin Akyurek]

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.

 

[Vanda_a]

It works perfectly. Thank you very much

And an other question please.
How the formula works if the column A is not numeric or it is text?

 

[Aladin Akyurek]

It works perfectly. Thank you very much

Glad to help.

And an other question please.
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.

 

[Vanda_a]

=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

 

[Aladin Akyurek]

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)))