Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Dynamic Named Range

  1. #1
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Named Range

    What kind of data do you have in each column?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Named Range

    Quote Originally Posted by Aladin Akyurek View Post
    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 by Vanda_a; May 31st, 2015 at 08:27 AM.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Named Range

    Quote Originally Posted by Vanda_a View Post
    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?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Named Range

    Quote Originally Posted by Aladin Akyurek View Post
    Column A is numeric, right?
    Yes

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Named Range

    Quote Originally Posted by Vanda_a View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Named Range

    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?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Named Range

    Quote Originally Posted by Vanda_a View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Oct 2012
    Posts
    920
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Dynamic Named Range

    Quote Originally Posted by Aladin Akyurek View Post
    =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 by Vanda_a; May 31st, 2015 at 09:57 AM.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Dynamic Named Range

    Quote Originally Posted by Vanda_a View Post
    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)))
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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