wildcard in named range in formula
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: wildcard in named range in formula

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default wildcard in named range in formula

    Doing calculations for my fantasy NFL teams.
    I have a sheet with multiple named ranges, FPweek1, FPweek2, etc .
    Can I ref that range in a formula, (vlookup in my case) where the number changes based on a number at the top of the column . ex: vlookup(playername,FPweek(a1),4,false) - where A1 has the value 1
    so when I copy this formula to the next column the FPweek# changes.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,103
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: wildcard in named range in formula

    Quote Originally Posted by freakofnature View Post
    Doing calculations for my fantasy NFL teams.
    I have a sheet with multiple named ranges, FPweek1, FPweek2, etc .
    Can I ref that range in a formula, (vlookup in my case) where the number changes based on a number at the top of the column . ex: vlookup(playername,FPweek(a1),4,false) - where A1 has the value 1
    so when I copy this formula to the next column the FPweek# changes.
    You should be able to use the INDIRECT function. See if something like this works...

    VLOOKUP(playername,INDIRECT("FPweek"&A1),4,FALSE)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: wildcard in named range in formula

    Try

    =VLOOKUP(playername,INDIRECT("FPweek"&A1),4,0)
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: wildcard in named range in formula

    However, the indirect method won't work if your named ranges are dynamic (using functions like counta to define row numbers).
    In that case, you need to do something like

    =VLOOKUP(playername,CHOOSE(A1,FPweek1,FPweek2,FPweek3,etc.),2,0)
    Last edited by Jonmo1; Jun 8th, 2018 at 12:54 PM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,103
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: wildcard in named range in formula

    Quote Originally Posted by Jonmo1 View Post
    However, the indirect method won't work if your named ranges are dynamic (using functions like counta to define row numbers).
    It seems to work when I try it... can you give me an example?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Jan 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: wildcard in named range in formula

    In my case the ranges are set at this point, FPweek1 won't really change.

  7. #7
    New Member
    Join Date
    Jan 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: wildcard in named range in formula

    & thx I'll try INDIRECT, I was looking at it but have no experience with it so wasn't sure.

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: wildcard in named range in formula

    Quote Originally Posted by Rick Rothstein View Post
    can you give me an example?
    ABCDEFG
    11a1
    2b2
    3b#REF!2c3

    Sheet1



    Worksheet Formulas
    CellFormula
    B3=VLOOKUP(A3,INDIRECT("FPweek"&A1),2,0)
    C3=VLOOKUP(A3,FPweek1,2,0)

    Workbook Defined Names
    NameRefers To
    FPweek1=OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F:$F),2)

    Last edited by Jonmo1; Jun 8th, 2018 at 01:19 PM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,103
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: wildcard in named range in formula

    Quote Originally Posted by Jonmo1 View Post
    Workbook Defined Names
    Name Refers To
    FPweek1 =OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F:$F),2)
    Sorry, my fault... I read what you wrote incorrectly. You did say the defined name was dynamic and for some reason, I misread that as referring to the formula in the cell that was producing the number. Thanks for following up.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: wildcard in named range in formula

    Quote Originally Posted by freakofnature View Post
    & thx I'll try INDIRECT
    You're welcome.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

Tags for this Thread

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
  •