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.

 

[Aladin Akyurek]

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)

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.

Great.

 

[rilzniak]

Aladin, Joyner,

If I used a macro to import the data from the website directly to my sheet would it allow me to create an Excel Table out of the data? I think it would but just want to confirm before I fumble my way through figuring that out.

The reason that I ask is that right now my sheet links to another file which won't allow me to have the Excel Table although this may be a possible solution and allow me to eliminate the need for the formulas referencing the named ranges.

 

[Aladin Akyurek]

Aladin, Joyner,

If I used a macro to import the data from the website directly to my sheet would it allow me to create an Excel Table out of the data? I think it would but just want to confirm before I fumble my way through figuring that out.

The reason that I ask is that right now my sheet links to another file which won't allow me to have the Excel Table although this may be a possible solution and allow me to eliminate the need for the formulas referencing the named ranges.

No idea. In fact, it's not clear to me what your processing needs are. Can you elaborate on that with examples, but not with formulas?

 

[rilzniak]

Can you elaborate on that with examples, but not with formulas?

I pull data from a website using an Excel Add-in and then link to that workbook (which is why I couldn't use the Excel table). What I'm wondering is if there's VBA code that would pull the data from the website and input it to the Excel table worksheet so the range is continually updated? I know that Tables can't link to external data (or so Excel error messages tell me) but I was wondering if there's a macro that could do it.

I did find another way though - using the Get External Data>From Web. The data needs a little work to be useful but if I believe I can modify it and the link should still work with the remaining figures.

Thanks guys.

 

[Aladin Akyurek]

I pull data from a website using an Excel Add-in and then link to that workbook (which is why I couldn't use the Excel table). What I'm wondering is if there's VBA code that would pull the data from the website and input it to the Excel table worksheet so the range is continually updated? I know that Tables can't link to external data (or so Excel error messages tell me) but I was wondering if there's a macro that could do it.

I think that should be possible. However, you need confirmation from some into VBA.

I did find another way though - using the Get External Data>From Web. The data needs a little work to be useful but if I believe I can modify it and the link should still work with the remaining figures.

Thanks guys.

I recall posts confirming that such is workable set up.