I'm currently trying to correct an Excel timesheet for the company I'm working for. For their system, employees have access to a workbook with 52 sheets - each sheet has the date of the Friday for the week as its name. The idea is that every employee can simply input the time they've spent working onto their own timesheet, and this will automatically update the master timesheet.
On the master timesheet, the A column has a list of every date for all of the Fridays in a year. In the B, C, etc. cells, I need to set each cell's formula to reference a certain sheet (for a specific date which matches the date listed in the A column) in another workbook.
Ex: E13, for instance, should reference the file [ts_2005_mfc.xls], the sheet named 11-Feb-05, and the cell W35. E14 should reference the same file and cell number, but it should be the sheet for 18-Feb-05. This continues for every Friday of the year.
While I have no issue accessing the other file, the specific sheet, and the correct cell, I'd like to automate which sheet each cell references. Currently I need to manually change which sheet is referenced for each cell, but a macro of some sort or an easy feature that I'm missing to fix this would be wonderful.
Just for extra clarification, the formula for a cell currently reads:
And the cell beneath is is exactly the same, but says 25-Feb-2011 instead of 18-Feb-2011.
While the initial example I gave is from 2005, the spreadsheet for 2005 is perfectly functional and I'm looking at it as reference for the 2011 timesheet. If there's an easy way to display the original formula in the 2005 sheet so that I can copy it to the 2011 sheet, that would be terrific.
Perhaps it would be easier to reference the cell in the A column with the appropriate date. However, I'm having trouble referring to both a cell in the current file as well as the specific cell in the other sheet and workbook within the same formula.
I've considered using the INDIRECT function for this, and I initially set up the entire master timesheet to work properly using the INDIRECT function. However, I've been told that a way to do this without the INDIRECT function is strongly preferred because it apparently takes awhile to load the other timesheets when using the INDIRECT function. I then considered using the indirect.ext option, but my understanding is that I would need to download and install that on all of the company's computers for every employee to be able to update their timesheet file from their company computer.
Thank you very much for all of your time and help.
I would like to apologize for the poor name of this topic. I had named it something else, but I think that ended up getting changed to the mere text I had input into Google for my search. If someone would like to fix the name of the topic to something better suited to the content of my question, I would have no problem with that.