Articles

Solved excel vba macro increment date

July 22, 2011 at 10:56:50
Specs: Windows XP

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 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:
='[filename.xlsm]18-Feb-2011'!$W$35
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.


See More: excel vba macro increment date

Report •


#1
July 22, 2011 at 21:52:38
✔ Best Answer

Perhaps you could use the INDEX function instead of INDIRECT. INDEX is non-volatile so it shouldn't slow the workbooks down.

In order to set this up, you'd need to create 52 Defined Names, such as Week1, Week2, etc.

Each name would refer to ='[filename.xlsm]A Weekly Sheet Name'!$1:$65536

e.g.: ='[filename.xlsm]18-Feb-2011'!$1:$65536

(The 65536 is a throwback to Excel 2003 to include the entire sheet. You could use whatever number you want, as long as the range contains all of the rows you are using in your weekly sheets)

See here for how to set up Defined Names to external references:

http://office.microsoft.com/en-us/e...

Then you could use:

=INDEX(Week1, 35, 23) to return $W$35 from whichever sheet Week1 refers to.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 1, 2011 at 07:27:25

Is there a way to automate it so that I don't need to enter in the exact information for every cell? I have 52 weeks of cells and 10 columns for each timesheet, with multiple timesheets to do.
Using the above method, it seems as if I would need to change the information for every cell. Also, it seems as if the 2005 spreadsheet doesn't use multiple defined names like this solution would.

For some reason, this seems to be returning a circular reference error as well.

Thank you for the solution you provided. I'm sorry it's taken me a week to respond - I had an operation and was out during that time.



Report •

#3
August 1, 2011 at 09:41:08

re: "Is there a way to automate it so that I don't need to enter in the exact information for every cell?"

Keep in mind that I am not as familiar with what youa re trying to do as you are, so I can't answer that. Maybe with an explanation of what you mean by "enter in the exact information for every cell" I might be able to offer a suggestion.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 1, 2011 at 11:02:53

Okay, please let me know if this gives a better explanation of what I'm trying to do.

The workbook I'm referencing has 52 sheets, with each sheet bearing the name of a certain date. For every sheet, there are specific cells I need to reference for different types of work, ie management, training, etc. Therefore, every cell in the workbook needs to reference a separate cell in the other workbook.

Ex: Cell E12 should reference the sheet named 4-Feb-11 and cell W35 in FileA; cell F13 should reference the sheet named 11-Feb-11 and cell W36 in FileA.

In reference to your quote, I mean that I would need to change the date (or change the Defined Name) manually every time I were to link one cell to another. Instead of continually changing

=INDEX(Week1, 35, 23) to =INDEX(Week2, 35, 23), Week3, and so on, I would like to be able to set the cells to automatically increment the defined name so I don't need to do that on a cell-by-cell basis.

Please let me know if I can be more coherent and I will try to clear up any questions you may have.

Edit (as of 8am PST, August 2nd):
Here's a URL to a screenshot that may be more helpful in explaining.

http://imageshack.us/photo/my-image...
The cell with the formula showing is for E11. As you can see, the formula includes the date 28-Jan-11, which is also the date in A11. 28-Jan-11 is the name of the sheet in the other workbook that is being referenced. The next cell references 4-Feb-11 and so on.
The cell W35 does not change within columns, but it will change from column to column. So I'd like to be able to automate a change in the date from cell to cell.

I'm sorry that my explanations are lengthy and probably redundant and unclear, but I hope the image is helpful in explaining. And I'd like to thank you for your help.


Report •


Ask Question