Reference data from another workbook based on the worksheet name

[mutzie]

There are 2 files attached, hoping you can look into it and provide insights:

Book.xlsx: Will be the main worksheet that will display data from the cells of another workbook. Book.xlsx will have multiple sheets which will be named from the months of the year affixed with Week 1 to 5, starting with May Week 1, May Week 2, May Week 3 etc. The sample file contains May Week 1 to December Week 5.

Score Data: Acts like a dump from which Book! will pull the values from. It contains data from May Week 1 to December Week 5 horizontaly, each column starting from Column F will be for 1 worksheet each.

Is it possible for example, for Book!"May Week 1" E18 to reference the value from Score Data!"Operations" F2 by comparing the sheet name of Book! to the identical named column on the other workbook?

The idea is for each worksheet to pull data from the corresponding columns of the other workbook.

Thank you so much!

[FDibbins]

You could do this with INDIRECT, but both workbooks will need to be opened.

[mutzie]

Hello FDibbins, thank you! I have no problems with the workbook being opened since after referencing, I'll have it copy pasted values. The Data Scoring is a one time thing only just to transfer the data to the main book. I know how indirect functions on a basic level, how can it be combined to lookup the value based on the sheet name?


Update: I think i made it work by using hlookup! wow, I can't believe it worked, aha moment.

[FDibbins]

Awesome!! Always a good feeling when you work it out yourself

care to share what you did?