Applying dupicate Column Headers in an Excel Table

[Denny57]

I have a table in which I would like to apply Days, Hours and Wages in columns for a number of staff members. To keep all the staff reecords visible I was hoping that I could use the same headings multiple times instead of using unique column headers, then add the name of the staff member to merged cells in the row above each set of three columns.

I only want to maintain weekly records for the most recent 52 weeks so as each new record is added I want the earliest record to be deleted. I have the code to run this in the worksheet.

One solution to the naming protocol would be to use separate tables for each employee, however the code does not work with multiple tables.

I am hoping that there are possible solutions which will either
a) allow duplicated column headers in the same table or
b) an altered code to allow the earliest record to be removed as soon as a new record is added to any table in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet.ListObjects(1)
    If Not Intersect(Target, .DataBodyRange) Is Nothing Then
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Do Until .DataBodyRange.Rows.Count <= 54
        .ListRows(1).Delete
      Loop
      Application.EnableEvents = True
      Application.ScreenUpdating = True
    End If
  End With
End Sub

Many thanks

 

[awoohaw]

For more scalable (and as yet unchosen reporting methods) why don't you just create a row header for each employee and have only the three additional columns?
You can then filter, create pivots, have slicers to look at your data in many more effective ways.

 

[Denny57]

For more scalable (and as yet unchosen reporting methods) why don't you just create a row header for each employee and have only the three additional columns?
You can then filter, create pivots, have slicers to look at your data in many more effective ways.

Thank you for your suggestion.

If I can find a way to create a database which does not use a table but will only retain 52 lines of data after the 2 header rows, then this solution will work.

I have no need to use the data for creating reports and piviot tables, simply to use the data to determine holiday pay entitlement.

If there is a way to maintain a database which will only contain a specific number of rows of information, then such code would be most welcome.

 

[awoohaw]

I'm not really understanding your reluctance. Although, you know your needs better than me.

But generally the best use of data in excel is to have data in one place and analysis/reporting elsewhere, combined. And yes, calculating that entitlement is a form of reporting or analysis. Choosing to delete old records is your choice and I have no say in that. In the scenario I suggest you can also delete old records via VBA as well.

How are you calculating the entitlements? Are these also a column for each employee beside Days Hours and Wages.

Oh, and I erred. It should be 5 or 6 columns: Name, Date, Days, Hours, and Wages..... And I'm guessing another one for the Holiday entitlement.

And, if you still want to do your method, a sample would be helpful as well. The xl2bb add in (link below) is encouraged. If you can't use it, a table of your data would be great. Please do not use an image.

Thanks!

 

[Denny57]

I'm not really understanding your reluctance. Although, you know your needs better than me.

But generally the best use of data in excel is to have data in one place and analysis/reporting elsewhere, combined. And yes, calculating that entitlement is a form of reporting or analysis. Choosing to delete old records is your choice and I have no say in that. In the scenario I suggest you can also delete old records via VBA as well.

How are you calculating the entitlements? Are these also a column for each employee beside Days Hours and Wages.

Oh, and I erred. It should be 5 or 6 columns: Name, Date, Days, Hours, and Wages..... And I'm guessing another one for the Holiday entitlement.

And, if you still want to do your method, a sample would be helpful as well. The xl2bb add in (link below) is encouraged. If you can't use it, a table of your data would be great. Please do not use an image.

Thanks!

I am currently trying to change the way that holiday pay is calculated. The correct method is to pay an average of weekly pay over the past 52 weeks, however, historically the business has been paying the average weekly hours multiplied by the hourly pay rate.

I also need to be able to work out an avaerage daily rate where odd days are taken, either using hours by rate or an average daily wage.

I intend to calculate all these options in a separate spreadsheet using the most recent 52 weeks information of Days, Hours & Wages for each employee

To achieve this I was looking to use a table which would retain just 52 rows of information below the header row. The code I have does this, however, I cannot use duplicated column headers.

If I do not use a table, then I need some code that would allow information to be stored in rows 4 thru 55 (52 rows) in a worksheet and when multpile pieces of data are added into row 56 that row 4 is deleted (Thus maintaining the last 52 weeks information)

Such code can be applied either through a control button or by code in the worksheet.

 

[Denny57]

I am currently trying to change the way that holiday pay is calculated. The correct method is to pay an average of weekly pay over the past 52 weeks, however, historically the business has been paying the average weekly hours multiplied by the hourly pay rate.

I also need to be able to work out an avaerage daily rate where odd days are taken, either using hours by rate or an average daily wage.

I intend to calculate all these options in a separate spreadsheet using the most recent 52 weeks information of Days, Hours & Wages for each employee

To achieve this I was looking to use a table which would retain just 52 rows of information below the header row. The code I have does this, however, I cannot use duplicated column headers.

If I do not use a table, then I need some code that would allow information to be stored in rows 4 thru 55 (52 rows) in a worksheet and when multpile pieces of data are added into row 56 that row 4 is deleted (Thus maintaining the last 52 weeks information)

Such code can be applied either through a control button or by code in the worksheet.

Another solution would be if I could create some code that would count / sum function only information held in the last 52 rows of information in a database. This would avoid the use of a table and would allow reference back over a longer period of time.

 

[awoohaw]

What is the difference between weekly pay and weekly hours * wage? or are you saying they have been averaging weekly pay for all pay periods instead of weekly pay for only the last 52 pay weeks? Nevermind, I think I got it. I Think I understand how an average daily pay may be different than average weekly
pay if overtime is involved. But I may be wrong.