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?
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
0
Bob BarnesAuthor Commented:
Thank-you so much, that is perfect.
0
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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.
0
Rgonzo1971Commented:
Could you send a dummy?
0
Bob BarnesAuthor Commented:
Will do Rgonzo1971, but tied up until later today. Thanks again. Bob
0
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
0
Rgonzo1971Commented:
HI,

pls try in A4

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

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

Bob
0
Rgonzo1971Commented:
(replaced ; with ,)

Now

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

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
0
Bob BarnesAuthor Commented:
Instant response, terrific help. Can't praise Rgonzo1971 enough.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.