Redirect a formula based on another cell value?

[Defski]

Hello,

I have a question is it possible to redirect a formula based on another cells value.

A little background, I have a workbook where I have a summery sheet and the rest of the sheet names are the week of the year. something like wk1, wk2, wk15 etc. I have several formulas on the summery sheet that may reference data on the various different week sheet names.

What I would like to do, for example is if I enter a week number in a particular cell, say I enter 15 in cell c1, the formula on this sheet that is referencing the various number of the week sheet names would then automatically start referencing the new week number entered, in this example wk15 instead of the previous week it was referencing say wk2.

Is anything like this possible?

 

[etaf]

using an indirect()

so you can enter a sheet name in a cell and then use that as the sheet name
BUT
how many formulas ?

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[Defski]

Thank you, you pointed me in the direction I needed and was able to come up with something that works.
for my purposes a formula like this worked well. =INDIRECT("'["&$Y$2&"]"&"wk"&$X$2&"'!K"& ROW(76:76))

 

[etaf]

you are welcome, glad its resolved for you