Increment Date/Sheet Name in External Reference

  • Hi,

    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 that 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.

    Here's a link of how the master timesheet appears:…/225/excelscreenshot.png/

    As you can see, in cell E11, the formula for the cell ends in ...\[filename]28-Jan-11'!$W$35. E12 should have the exact same formula, except it should reference the sheet titled 4-Feb-11, so the formula would be ...\[filename]4-Feb-11$W$35. The purpose of this is to reference a specific cell on a specific sheet in the given file. This trend of incrementing the date but keeping the cell the same goes on for all 52 weeks of the year. The next column references a separate cell, but I'd be happy with something that I can just automate for one column and repeating/changing that for every column in the sheet.

    I was able to do this using the INDIRECT function (which is why there are #REF! errors in the spreadsheet,) but was asked to find a way that was a little faster and more manageable. I'm not quite experienced enough to write a macro to do this, but it seems to me that there would be an Excel function that should make this simple.

    I understand that the forum rules ask to avoid cross-posting or to supply the link to the other forum if one does decide to cross-post. I have decided to post here as well since it has been a couple days since tmy initial topic has been answered. Here is that link:…increment-date/15067.html

    Using the INDEX function was suggested to me in that topic, but I couldn't get that to work and I couldn't find a way to automate it. In other words, I couldn't find a way that would enable me to change the defined name in a prompt manner rather than manually editing it for every cell in the spreadsheet.

    Please let me know if I can be clearer in explaining the problem I'm having and what I'm trying to achieve. I really do appreciate any and all help provided, and I apologize if I've done something incorrect in cross-posting or if I've made note of that improperly.

    Thank you for your help.

  • Re: Increment Date/Sheet Name in External Reference

    I'm not sure that Replace would let me set each cell's formula to the corresponding date. I could use replace to get rid of all of the REF! errors, sure, but how would I set it so that cell E11's formula is [filename]28-Jan-11'!$W$35, cell E12's formula is [filename]4-Feb-11'!$W$35, and cell E13's formula is [filename]11-Feb-11'!$W$35?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!