Excel macros between two worksheets

Topic

New Reply

We use the attached “Individual Employee Vacation Log” file to enter our employees’ work hours, which determines the amount of vacation they earn.  It is set up so all we do is enter their weekly hours and the formulas do the rest of the work.  What we need is to have, every week, the most recent “Available Hours” figure from that file automatically populate the “Accrued Hours” cell in the “Accrued Vacation Hours” file.  There is a formula there to subtract any vacation hours used to update the vacation balance.  Also, if there is a way to automatically update the check date, pay week and week #, that would be a big plus.  Any questions, let me know!  Thanks for your help, as always!

Reply | Quote

Replies

This probably can be done – Excel is a Turing complete language and can do most things – but some types of jobs are meant to be done in databases, not spreadsheets.  People, their hours worked, the check number they received.  All of this sounds ideal for a database and if you do it in a spreadsheet it will be hard in the future for people to follow your formulas.

Reply | Quote

Thank you for your suggestion.  At this time, however, no one where I work uses a database (Access) for anything.  We have the program but do not utilize it at this point.  Unless there is a way to import the data from Excel to Access, at this point I don’t think it would be worth it to us to “reinvent the wheel”.  I am familiar with Access and use it for projects outside of work, but we are a very small company (3 office personnel that use a computer) and we mostly use QuickBooks and Excel.

Reply | Quote

For a company with 3-10 employees I endorse your choice to use Excel.

Reply | Quote

I’m certain you can do this, although I’ve never tried it. I think the easiest thing would be to link sheets within a workbook. There’s plenty of examples on the internet such as this one.

https://smallbusiness.chron.com/2-excel-spreadsheets-43626.html

Reply | Quote

Okay, I’m not always the best at explaining my objectives.  In the attached Word file is a screenshot of the two aforementioned documents with highlighted cells.  My objective is to have Employee #1’s yellow highlighted number in Cell E5 on the lefthand sheet transfer to the yellow highlighted cell (G6) on the righthand sheet.  Then the next week it will be Cell E6 on the lefthand sheet that will need to be transferred to Cell G6 on the righthand sheet, and so on down each succeeding week.  Obviously, Employee #2 will have to have different references.

Also, it would be great if the “Check Date”, “Pay Week” and “Week Number” fields could be automatically updated each week.  I have another file that was set up years ago with the help of either Maudibe (sp?) and/or Zeddy that has a separate sheet for a calendar; then they used formulas that include “IF” and “MATCH” to populate for weekly updates on other sheets.  I don’t know how to do any of that, I can just see that’s what they used.  Maybe I should put the work sheets in one workbook?

Thanks again for any help!

Reply | Quote

If you want to send screenshots, save them as PNG and attach to the post. Saves us downloading and opening a document.

cheers, Paul

Reply | Quote

Okay, it’s been awhile since I’ve been able to get back to this.  Since I’ve changed the screen shot, I will restate my objectives.  I have entered only one employee for an example; each employee has its own tab on the actual worsheet:.

Every week I enter the “Total Hours Worked” and the formula calculates the “Wkly Vacation Hours Earned”, which in turn calculates the “Total Vacation Hours Earned”.  I then enter any “Vacation Hours Used”, which then calculates the “Available Vac Hours”.  There is a formula in “Net Vacation Hours” to account for any vacation hours used the previous week.  I know I have to enter the “Total Hours Worked” and “Vacation Hours Used” every week, which is no problem.  To reduce data entry errors, can someone come up with a formula to enter into the “Total Vac Hours Earned” and “Vacation Hours Used” on the “Vacation Accrued Hours 2022” worksheet?  There is already a simple subtraction formula in the “Available Vac Hrs” (E6).  Thanks for any help you can give!

Reply | Quote

Is there a reason you show hours as decimal instead of HH:MM?
You can enter the time more easily.

Net and available vacation hours are the same. Why 2 columns?

Total Hours is =MAX(D:D)
Vac Hours Used is =SUM(F:F)

cheers, Paul

Reply | Quote

Concerning showing hours as decimal instead of HH:MM, that is the way our payroll company wants us to report the hours, so that’s the way we have to do it.  Totally understand your reasoning, however.

Not sure what to do with this:

Total Hours is =MAX(D:D)
Vac Hours Used is =SUM(F:F)

Reply | Quote

WSk32rem wrote:

a formula to enter into the “Total Vac Hours Earned” and “Vacation Hours Used” on the “Vacation Accrued Hours 2022” worksheet?

=MAX(D:D) will give you Total Vac Hours Earned. You will need to enter the formula by hand and then point to the sheet and range D:D (click the D column).

=SUM(F:F) gives Vacation Hours Used

cheers, Paul

Reply | Quote

Paul, I am sorry I am so dense as to your instructions.  I tried entering the formula =MAX(D:D) in the Vacation Accrued Hours worksheet in the Total Vac Hours Earned cell and then tried pointing to Column D in the Attendance Log sheet but I’m not sure that’s correct.  Regardless, it didn’t work.  I guess I need to be guided specifically the worksheet and cell to enter the formulas.  Again, sorry to not understand what you are telling me to do.

Reply | Quote

You can part enter a formula and then use the mouse to select a worksheet/cell.

Type =max(
Switch to the other sheet via the sheet listing menu and click on the column heading for D.
Press Enter.

cheers, Paul

1 user thanked author for this post.

WSk32rem

Reply | Quote

Works perfectly!  Thank you SO much!

1 user thanked author for this post.

Paul T

Reply | Quote

WSk32rem,

Take a look at the formulas in this workbook.
CMV1-0-Employee-Accrued-Vacation-Hours
Note the use of Indirect to matchup with different employees based on Tab name. The formulas in the Summary sheet can be filled down as can the ones in the employee sheets.
For some reason your Net Vacation is .01 off starting in 2/5/2022?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

WSk32rem

Reply | Quote

If it were my sheet I’d have a page for entering the data in HH:MM and a section that shows the totals in decimal for your payroll company. I hate decimal hours!

cheers, Paul

Reply | Quote