Variable sheet names

I have two workbooks APC-15.xlsm and Template.xlsm - Workbook APC-15 has numerous worksheets, Template.xlsm has only one worksheet.

I am attempting to make Cell A4 in Template.xlsm equal to Cell A7 in sheet WK24 of APC-15.xlsm, which I have accomplished with the formula in A4 of ='[APC-15.xlsm]WK24'!A7

Is it possible to replace the sheet name WK24 with a variable reflecting an entry in Cell A1 of Template.xlsm so if Cell A1 reads Wk25 then Cell A4 in Template.xlsm will be equal to Cell A7 in sheet Wk25 of APC-15.xlsm

Thank you anyone for any assistance
Bob BarnesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

you could use

=INDIRECT("'[APC-15.xlsm]"&A1&"'!A7")

EDIT does not work with closed workbooks

Regards
Bob BarnesAuthor Commented:
Thank-you so much, that is perfect.
Bob BarnesAuthor Commented:
Sorry Rgonzo1971, I was a bit hasty. I should have mentioned that it is my intention to drag the formula to populate other cells, but when I tried the Cell A7 does not update to Cell A8 etc.. I can make the changes manually, but there are at least 200 entries which vary week by week. Is there a solution to this?

Thank-you
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Rob HensonFinance AnalystCommented:
I assume your Template file is so that you can copy that sheet into the other file as required.

If so why not have the Template sheet as an extra sheet in the file, you will then be able to use the INDIRECT function as described above without restriction for the file being open.
Rgonzo1971Commented:
Could you send a dummy?
Bob BarnesAuthor Commented:
Will do Rgonzo1971, but tied up until later today. Thanks again. Bob
Bob BarnesAuthor Commented:
Hello Rgonzo1971. I have edited the attached workbooks to just a few rows, so that you can see exactly what I am attempting. In cell A4 of Template.xlsm, I have entered the suggested formula, which works perfectly but, if I grab the corner node and drag it down the column, in an attempt to populate all the cells with the same formula, each on being incremented by one cell number, that doesn't happen. It just repeats the information in cell A4 so all the cells are looking at Cell A4 not A4, A5, A6 etc. The complete workbook has over 200 rows, which varies from week to week, so changing them all manually would be quite labour intensive.

Thanks for any added help you can offer.

Bob
APC-15.xlsm
Template.xlsm
Rgonzo1971Commented:
HI,

pls try in A4

=OFFSET(INDIRECT("'[APC-15.xlsm]"&$A$1&"'!a7");ROW(A7)-7;COLUMN(A7)-1)

Regards
Bob BarnesAuthor Commented:
Thanks, but when I try entering the formula, Excel reports that the formula contains an error.

Bob
Rgonzo1971Commented:
(replaced ; with ,)

Now

=OFFSET(INDIRECT("'[APC-15.xlsm]"&$A$1&"'!a7"),ROW(A7)-7,COLUMN(A7)-1)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob BarnesAuthor Commented:
Your genius is only supersede by your kindness. Thank-you so much, all is now perfect in the world of Excel formulas.

Best regards

Bob
Bob BarnesAuthor Commented:
Instant response, terrific help. Can't praise Rgonzo1971 enough.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.