VBA to define alias for workbook location to be used in formula

Hi folks,

The hospital I work for provides an excel workbook for each employee in which they have to keep track of their working hours, sick-leave, holidays etc (called a jaarkaart in Dutch).
I'm trying to make a separate excel workbook in which an automatically updated overview will be made of the data each of the people in our department fills in in his/her jaarkaart.
Each day is represented in the jaarkaart by a block of 4 cells, the top two of which are used to fill in the working hours (am and pm) and the bottom two are used for codes to indicate illness, days off etc (see attached example).

Currently, my overview spreadsheet is filled with formulas such as this:

Please Login or Register  to view this content.

Four of these per day for each employee.....
So you can probably imagine that even for our small group of 12 people, when I try, for example, to update 2016 to 2017 by find-replace all....excel crashes because that's too much work.

Therefore, I was wondering if it is possible to define aliasses for the locations of the jaarkaarten in VBA and use those aliasses in the formulas, so that the formulas will end up looking something like this:

Please Login or Register  to view this content.

I tried making a private sub which basically contained the following for each employee:

Please Login or Register  to view this content.

But that didn't work.

Hope you can help me out.
Thanks in advance.
Thirsa

Hi Richard,

I agree with you that using something like google forms would be easier, but that would mean that we would be using a different system then the rest of the hospital and I don't think the human resources department of the hospital will accept that (they keep a central registration of all cards of all hospital employees, so naturally they want everybody to use the same system). So the only way to use google forms is to have our employees fill in the excel sheet for the HR-dept and the google form for our own overview. Knowing how busy everybody's schedules are, I can tell you that that won't happen.

So indeed it might be easiest to simply create direct links instead of my crazy formulas. The reason I had those in my current version is that I was planning on using conditional formats to colour code the overview and to keep the conditions as straightforward as possible, I need all four cells of each day to contain information. And unfortunately, that is not always the case: When the info for the morning and afternoon are the same, most people only fill in the info in one set of the cells...and some use the am cells for that whereas others use the pm cells.

But I guess that making the formatting conditions a bit more complex is less stressful for Excel than my current crazy formulas, so I think I'll take your advice and use direct links.

Thanks

Hi Richard,

Unfortunately, our HR folks (or the IT folks....don't know who to blame here) can be pretty stubborn, so I doubt that I'll be able to convince them to start using a different layout.
But....it never hurts to try....

Thanks again for thinking along.
Thirsa