Named Range, VLOOKUP, and INDIRECT Problem

[rilzniak]

Hi everyone!

I'm creating a spreadsheet to track points for a hockey league and have a Rank table which shows calculations of each of the players. I'm trying to automate it as much as possible (because I'm lazy) but I've run into a problem with regards to my Named Range VLOOKUP.

I've created a table which references the sheet names (A) and a range within that sheet (B), which automatically gets adjusted when new entries are created. Here's an example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]_13[/TD]
[TD]=A1&"!$B$2:$B"&COUNTA(INDIRECT(A1&"!A:A"))-1[/TD]
[TD]=INDIRECT($B$1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]_12[/TD]
[TD]=A2&"!$B$2:$B"&COUNTA(INDIRECT(A2&"!A:A"))-1[/TD]
[TD]=INDIRECT($B$2)[/TD]
[/TR]
</tbody>[/TABLE]

Column A contains the name of the Named Range in the Name Manager.
Column B contains the formula for the Named Range.
Column C contains the formula in the Name Manager.

I've got a cell which is named statyear and contains _13. What I want to be able to do is change the statyear to _12, or _11, etc. and have my VLOOKUP use the range specified in B1, B2, etc. as per that Named Range.

Here's an example of the formula I want to use: VLOOKUP("Sidney Crosby",statyear,COLUMN(B$1),FALSE)

How do I get the statyear (_13) to look in that named range (_13) without having to enter _13 in place of statyear? I've tried a number of INDIRECT combinations but can't seem to get it to work. Any suggestions are welcomed.

This makes sense to me as I'm typing it but it may be confusing to the readers so please ask any questions and I'll help clarify when needed.

 

[Joyner]

First, I'll just say that if it were me, I would use Excel Tables instead of the dynamic Defined Name you are using. Tables are dynamic so they will automatically expand when new entries are added, whether you use the Table references of Defined Names in the table.

For the Statyear part, I’m not real clear on how/where you want to change the reference from _13 to _12 etc. Is there a separate formula for each stat year? If a single cell in named statyear, then you would need to change the value in that particular cell for the change to reflect in the formula – use could use a drop down list for that. I may need to understand more about where the formula will reside and are you copying down a column, etc to fully understand what you need.

If _13 and _12 are sheet names, if you use Excel Tables, then you can just reference the Tables and won’t need to try to use INDIRECT to reference the sheet names.

A sample of the data layout and the expected results would really help.

 

[rilzniak]

First, I'll just say that if it were me, I would use Excel Tables instead of the dynamic Defined Name you are using. Tables are dynamic so they will automatically expand when new entries are added, whether you use the Table references of Defined Names in the table.

Unfortunately, that's not an option.

For the Statyear part, I’m not real clear on how/where you want to change the reference from _13 to _12 etc. Is there a separate formula for each stat year?

Yes.

A sample of the data layout and the expected results would really help.

Here's the link to a sample: https://drive.google.com/file/d/0ByIeJ17O_i8cUy1fRVNXWkh4a2c/edit?usp=sharing

Thanks for your help!

 

[Aladin Akyurek]

For example:

=COUNT(INDIRECT(VLOOKUP(statyear,A2:B3,2,0)))

But what advantages do you hope to have with such a set up?

 

[Joyner]

Unfortunately, that's not an option.

Yes.

Here's the link to a sample: https://drive.google.com/file/d/0ByIeJ17O_i8cUy1fRVNXWkh4a2c/edit?usp=sharing

Thanks for your help!

Hello,

A couple issues. First your lookup range starts in column A and it needs to start in column B. There are a couple other things but here is what I did:

Created a Defined Name "stats" refs to:

=INDIRECT(statyear&"!$B$2:$F$"&COUNTA(INDIRECT("'"&statyear&"'!A:A")))

And the VLOOKUP formula is:

=VLOOKUP("Sidney Crosby",stats,5,FALSE)

You don't need the other cells you use (A1:B3), just the statyear cell


Although, you can still use Tables - I haven't seen where converting a range to an Excel Table was not an option.

Let me know how the above works for you

 

[rilzniak]

For example:

=COUNT(INDIRECT(VLOOKUP(statyear,A2:B3,2,0)))

But what advantages do you hope to have with such a set up?

The formula just spits out a number and I'm not quite sure what it represents as it isn't the result I was looking for. The idea is that when you change the statyear from _13 to _12 that the formulas with the word statyear in them will know to look in the _12 named range. I'd like to be able to modify the referenced cells in the named range which is why I have them laid out on the sheet in the first place.

I understand that Tables may be an option although I'm linking to an external data source so it's not an option for me. Is there a better way I should be doing this?

 

[rilzniak]

=INDIRECT(statyear&"!$B$2:$F$"&COUNTA(INDIRECT("'"&statyear&"'!A:A")))

What does this return? I've played around with it but can't figure it out...

You don't need the other cells you use (A1:B3), just the statyear cell

I wanted to keep the table so that I could modify the named ranges within the cells on my worksheet.

Although, you can still use Tables - I haven't seen where converting a range to an Excel Table was not an option. Let me know how the above works for you

It works although I want to keep the named ranges on my worksheet so they can easily be modified if I need.

Also, the Excel Table doesn't seem to work for me because I have an external data reference. Thanks for your help.

 

[Aladin Akyurek]

The formula just spits out a number and I'm not quite sure what it represents as it isn't the result I was looking for. The idea is that when you change the statyear from _13 to _12 that the formulas with the word statyear in them will know to look in the _12 named range. I'd like to be able to modify the referenced cells in the named range which is why I have them laid out on the sheet in the first place.

I understand that Tables may be an option although I'm linking to an external data source so it's not an option for me. Is there a better way I should be doing this?

Your intent is not very clear. That you are not satisfied with the results op applying INDIRECT to statyear confirms that. If you want the points of Crosby in _12, you need in your set up something like:

=VLOOKUP("sidney crosby",
  OFFSET(INDEX(INDIRECT(VLOOKUP(statyear,A2:B3,2,0)),0,2),0,0,,5),5,0)

This yields 56 given that _12 refers to:

[TABLE="width: 353"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5347" width=150><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: black"]Rank[/TD]
[TD="class: xl83, width: 150, bgcolor: black"]Player[/TD]
[TD="class: xl66, width: 64, bgcolor: black"]GP[/TD]
[TD="class: xl66, width: 64, bgcolor: black"]G[/TD]
[TD="class: xl67, width: 64, bgcolor: black"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: black"]Pts[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: white"]1[/TD]
[TD="class: xl80, bgcolor: white"]Martin St. Louis[/TD]
[TD="class: xl69, bgcolor: white"]48[/TD]
[TD="class: xl69, bgcolor: white"]17[/TD]
[TD="class: xl69, bgcolor: white"]43[/TD]
[TD="class: xl70, bgcolor: white"]60[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #d9d9d9"]2[/TD]
[TD="class: xl81, bgcolor: #d9d9d9"]Steven Stamkos[/TD]
[TD="class: xl72, bgcolor: #d9d9d9"]48[/TD]
[TD="class: xl72, bgcolor: #d9d9d9"]29[/TD]
[TD="class: xl72, bgcolor: #d9d9d9"]28[/TD]
[TD="class: xl73, bgcolor: #d9d9d9"]57[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: white"]3[/TD]
[TD="class: xl82, bgcolor: white"]Alex Ovechkin[/TD]
[TD="class: xl75, bgcolor: white"]48[/TD]
[TD="class: xl75, bgcolor: white"]32[/TD]
[TD="class: xl75, bgcolor: white"]24[/TD]
[TD="class: xl76, bgcolor: white"]56[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #d9d9d9"]4[/TD]
[TD="class: xl81, bgcolor: #d9d9d9"]Sidney Crosby[/TD]
[TD="class: xl72, bgcolor: #d9d9d9"]36[/TD]
[TD="class: xl72, bgcolor: #d9d9d9"]15[/TD]
[TD="class: xl72, bgcolor: #d9d9d9"]41[/TD]
[TD="class: xl73, bgcolor: #d9d9d9"]56[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: white"]5[/TD]
[TD="class: xl82, bgcolor: white"]Patrick Kane[/TD]
[TD="class: xl75, bgcolor: white"]47[/TD]
[TD="class: xl75, bgcolor: white"]23[/TD]
[TD="class: xl75, bgcolor: white"]32[/TD]
[TD="class: xl76, bgcolor: white"]55[/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: #d9d9d9"]6[/TD]
[TD="class: xl85, bgcolor: #d9d9d9"]Eric Staal[/TD]
[TD="class: xl78, bgcolor: #d9d9d9"]48[/TD]
[TD="class: xl78, bgcolor: #d9d9d9"]18[/TD]
[TD="class: xl78, bgcolor: #d9d9d9"]35[/TD]
[TD="class: xl79, bgcolor: #d9d9d9"]53[/TD]
[/TR]
</TBODY>[/TABLE]

I would not recommend such a processing at all though.

 

[Joyner]

What does this return? I've played around with it but can't figure it out...

I wanted to keep the table so that I could modify the named ranges within the cells on my worksheet.

It works although I want to keep the named ranges on my worksheet so they can easily be modified if I need.

Also, the Excel Table doesn't seem to work for me because I have an external data reference. Thanks for your help.

=INDIRECT(statyear&"!$B$2:$F$"&COUNTA(INDIRECT("'"&statyear&"'!A:A")))

The above formula, saved as a Defined Name in the Name Manager, creates the expandable/dynamic range for the Range Lookup/lookup table for the VLOOKUP Formula (stats part). If you want to keep the other cells on your worksheet you can, but I'm not clear of the purpose if your intent is to use the VLOOKUP formula to adjust based on the statyear you selelct. If you need to adjust the formula/Define Name for other purposes please explain.

You say you are using an external data source, but obviously importing somehow, so if you don't want to use Tables, and convert the data everytime, that fine, you can simply use the formula as you orginally wanted.

But based on your orginal post, you wanted a VLOOKUP formula to pull data based on the statyear selected. The method you were trying was close, but not quite what you had to do to make it all work. If my approproate is not what you need, please clarify / explain what you need to accomplish. You probably intend to use the VLOOKUP formula a bit different than what you ahve simply laid out, so if there is anything you need to add for additional help, no problem.

Thanks

 

[rilzniak]

Your intent is not very clear. That you are not satisfied with the results op applying INDIRECT to statyear confirms that. If you want the points of Crosby in _12, you need in your set up something like:

=VLOOKUP("sidney crosby",
  OFFSET(INDEX(INDIRECT(VLOOKUP(statyear,A2:B3,2,0)),0,2),0,0,,5),5,0)

=INDIRECT(statyear&"!$B$2:$F$"&COUNTA(INDIRECT("'"&statyear&"'!A:A")))

Got it to work. Thanks guys! Sorry, I do have a hard time describing what I'm looking to do - terms are closely related so kind of confusing sometimes.