how to reference another workbook and a specific tab on that workbook

[chriswrcg]

In our warehouse we monitor daily productivity using a monthly spreadsheet that is broken down into the individual days of the month. Each tab of the workbook is labeled with the days number.

I am creating a new workbook that will need to reference the productivity workbook. The issue is as the months change so should the workbook being referenced as well as change the tab being looked at based on the day.

The way to do it would be to reference some cells on a daily entry page that would contain the workbook name to look for as well as a cell that picks what tab to look for. So I need to create a cell reference formula that looks at cell A1 to get the name and location of the workbook to be looked for and cell b1 to get what tab to look for, or something like that.

[6StringJazzer]

You can use the INDIRECT function to do that, but it requires that the monthly workbook be open, or you will get an error.

I think in your case a better option is to use VBA to build the formula each time the month changes.

Unfortunately the sample files you have provided are of no help. The just have three sheets with a single value on each sheet. You need to show us what you described as "some cells on a daily entry page that would contain the workbook name to look for as well as a cell that picks what tab to look for." And also sample formulas to show the data you want to reference. You can hard-code those formulas for now just to demonstrate what you want.

The VBA strategy is to use Worksheet_Change to detect changes to the month and day, and rebuild the formulas when that happens.