Advice For Leave Log/Leave Request Project: What's the best way forward?

Hello,

I have this leave tracker project I’m working on. There’s a required form that must be used. The form is outdated and hasn’t been updated in over 30 years. There are much better ways to track leave taken, but we are bound by regulation to use this form. What I’d like to do is maintain the tracking in an easy to use log and have the form populate the information from the log inputs. I’ve created the log and digitized the form and put it on its own worksheet. Now I need to come up with a strategy to make it work. I’ll attach the spreadsheet for reference. I think the form is self-explanatory enough to understand how a leave request is recorded. When leave is requested, a leave number is assigned to that request. They’re issued in the order they are received. There are anywhere from a few hundred to just over 1k requests any given year. The form, however, only allows 10 entries per page. So I’m trying to figure out the easiest way to automate the form.

I could copy and paste a couple hundred sheets one after the other and add formulas to each and every cell. There’s no easy way to do this except to manual input thousands of formulas because you can’t drop and drag the formula because of the way the form is laid out.

Or,

Maybe use 1 form and add a print button that uses VBA code that would use the log to input the fields on the form and loop through the log and print the pages off until it gets to the end. I can understand VBA to a degree, but in no way am an expert. I’m hoping there’s an easy solution that someone here might know that I’m too inexperienced to see.

Thank you all in advance!

Originally Posted by janmorris

"the best" way forward is to reorganise the data into a proper table format.. without merged cells, so that the data can be easily used in calculations.

I’m already using the leave log sheet as a table to do the calculations. Do you mean turning the form into a table? If so, that won’t work unless there’s a way to do it while making the form appear the same when printed.

There are many ways to do this more efficiently than using the form. That’s the issue though. My regulations require the use of the form. I would love to be able to type the information in the log or on table and be done with it. But I can’t because an official regulation says we must use the form. Despite how asinine and inefficient that may be.