+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    01-21-2021
    Location
    Mississippi, USA
    MS-Off Ver
    MS Office 16
    Posts
    2

    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!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    854

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

    "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.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    01-21-2021
    Location
    Mississippi, USA
    MS-Off Ver
    MS Office 16
    Posts
    2
    Quote Originally Posted by janmorris View Post
    "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.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,255

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

    Perhaps this will help:
    1. I suggest converting the data range on the FY22 LV LOG into an Excel table so that the (new) formulas on the AF 1486 AF Leave log (Master) will update when new rows of data are added
    2. Make the manual input of leave authorization in cells A2:B2 and use formulas fill cells utilizing leave authorization to reference those cells (i.e. A7:B7, A11:B11 etc.)
    3. Use INDEX/MATCH formulas to retrieve data from the FY22 LV LOG sheet
    For example MEMBER'S NAME/RANK & DUTY PHONE is retrieved using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As for printing I feel that your "use 1 form" option is best. I don't know enough about VBA, or printing for that matter, to be of help, but scanning the article linked below it would seem that you could print the form and then change B2 to load the next group of data to be printed (see the section: Choose what to print: selection, sheet or entire workbook).
    https://www.ablebits.com/office-addi....%20See%20More.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Leave balance adjustment calculation with multiple leave types
    By rithamworld in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2021, 01:39 PM
  2. Leave calendar to count number of days of leave left
    By Fads in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2021, 02:40 AM
  3. [SOLVED] Leave calendar to count the number of days of leave each staff has left
    By Fads in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-03-2021, 02:39 AM
  4. [SOLVED] How to leave leave cell blank if all cells added together = 0
    By jmitch26 in forum Excel General
    Replies: 3
    Last Post: 07-30-2019, 11:00 PM
  5. Replies: 6
    Last Post: 02-13-2019, 05:47 AM
  6. [SOLVED] Leave tracker - how to use formula to automate the total leave days
    By yiyi2 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-16-2018, 10:59 PM
  7. Replies: 5
    Last Post: 03-03-2015, 01:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1