wildcard in named range in formula

freakofnature

New Member
Joined
Jan 13, 2018
Messages
10
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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)
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Workbook Defined Names
NameRefers To
FPweek1=OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F:$F),2)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top