Excel Queries

Joined
1 Jan 2013
Messages
418
Likes
699
AFL Club
Melbourne
#1
Hi all

Excel can be fun and frustrating and I'm sure there are quite a number of people on the site using it to varying levels to help work out Supercoach scenarios and planning. I'm advancing my skills (mainly b/c of Supercoach) and am happy to help anyone where I can.

But to start this of I am posing a question that has me stumped in the hope that any EEs (Excel Experts) can help me progress.

I am starting up a series of workbooks in readiness for next year. And to create this, I am using this year's data and my weekly teams to get it started. I have all data in a separate workbook with sheets corresponding to each round. I then have my "Year" book which will have my team and weekly changes (I can then replicate the workbook for testing scenarios). In this workbook to save me retyping sheet names into formulas for each separate round, I'm sure I have seen somewhere where you can add the text from a cell (ie O1 in the image below) to match part of the formula (as highlighted). So when I replicate the sheet for Round 2, I can just put in R2 in O1 and it will look at the sheet [STATS Book.xlsx]R2_FF"!.
Sheetname_query.JPG

I have done some searching but can't phrase the search correctly to find what I need. Hoping someone will have some answers they can share.

Many thanks, Stephen
 
Joined
22 Oct 2014
Messages
7,031
Likes
36,110
AFL Club
North Melb.
#2
Hi all

Excel can be fun and frustrating and I'm sure there are quite a number of people on the site using it to varying levels to help work out Supercoach scenarios and planning. I'm advancing my skills (mainly b/c of Supercoach) and am happy to help anyone where I can.

But to start this of I am posing a question that has me stumped in the hope that any EEs (Excel Experts) can help me progress.

I am starting up a series of workbooks in readiness for next year. And to create this, I am using this year's data and my weekly teams to get it started. I have all data in a separate workbook with sheets corresponding to each round. I then have my "Year" book which will have my team and weekly changes (I can then replicate the workbook for testing scenarios). In this workbook to save me retyping sheet names into formulas for each separate round, I'm sure I have seen somewhere where you can add the text from a cell (ie O1 in the image below) to match part of the formula (as highlighted). So when I replicate the sheet for Round 2, I can just put in R2 in O1 and it will look at the sheet [STATS Book.xlsx]R2_FF"!.
View attachment 22384

I have done some searching but can't phrase the search correctly to find what I need. Hoping someone will have some answers they can share.

Many thanks, Stephen
You can use the INDIRECT function to achieve this. Essentially you can construct the text of a range name or a cell reference and then enclose it in the INDIRECT function and it will be treated as though it is that range name or cell reference within the formula.

The Vlookup function you have above would become the following;

VLOOKUP(B4, INDIRECT(“’[STATS Book.xlsx]”&O1&”_FF’!$AA:$AP”),3,FALSE)

Note that this formula appears to be in a separate file to the file you are seeking to extract the information from. In that case you have to ensure the source file is open. This technique won’t work if your source file is closed.
 
Last edited:
Joined
1 Jan 2013
Messages
418
Likes
699
AFL Club
Melbourne
#3
You can use the INDIRECT function to achieve this. Essentially you can construct the text of a range name or a cell reference and then enclose it in the INDIRECT function and it will be treated as though it is that range name or cell reference within the formula.

The Vlookup function you have above would become the following;

VLOOKUP(B4, INDIRECT(“’[STATS Book.xlsx]”&O1&”_FF’!$AA:$AP”),3,FALSE)

Note that this formula appears to be in a separate file to the file you are seeking to extract the information from. In that case you have to ensure the source file is open. This technique won’t work if your source file is closed.
Thank You B2D!!!

That's got it. I was just looking into the INDIRECT function and just working out how to try incorporating it into the formula.

Just took a bit of tweaking as I did a copy and paste from the above. It produced errors - but then I remembered about issue with " and ' when copied from web pages. I just pasted these into notepad, then replaced those with the keyboard equivalents and it worked like a charm!!!

Thanks for sharing!
 
Joined
22 Oct 2014
Messages
7,031
Likes
36,110
AFL Club
North Melb.
#4
Thank You B2D!!!

That's got it. I was just looking into the INDIRECT function and just working out how to try incorporating it into the formula.

Just took a bit of tweaking as I did a copy and paste from the above. It produced errors - but then I remembered about issue with " and ' when copied from web pages. I just pasted these into notepad, then replaced those with the keyboard equivalents and it worked like a charm!!!

Thanks for sharing!
Yes the single and double quotes can be a bit annoying when cutting and pasting from websites but well done for identifying and resolving that.

Happy to help. Anything else just post here.
 
Top