Jump to content



Photo

Excel 2010 Manage list of employees acrross sheets

excel

  • Please log in to reply
3 replies to this topic

#1 buzlink

buzlink

    Neowinian

  • Joined: 08-March 03
  • Location: Kansas City, KS

Posted 24 January 2012 - 03:59

Is there a way to manage a list of employee names broken out by department to have this same list update across other sheets in the workbook adding and deleting any changes to the employee list?

I am hoping to track a few metrics for each employee by each day for a month and then have this total update and track by each month for a year end total. Any suggestions on the best way to track this woudl be greatly appriceated as it is key to be able to add and delete employees and have this update across the other sheets.

Thanks.


#2 Xexo

Xexo

    Neowinian

  • Joined: 04-October 07

Posted 24 January 2012 - 09:46

It would be better to use an actual relational database IMHO, instead of Excel, but I know it is tempting to abuse Excel. Our users do the same :)

What is the layout ? Do you have separate sheets for each department ?
If the layout is consistent, then you can make some VBScript functions that query across the sheets, tallying up your metrics and returning them just like the premade excel functions do. Basically enumerating all sheets with a certain tag in the sheet name, then iterating through all lines until the script finds a blank line, treating each row like a database row.

It is possible, but if you have a lot of data ( thousands+ of rows ) then a real database will be a lot faster.

#3 OP buzlink

buzlink

    Neowinian

  • Joined: 08-March 03
  • Location: Kansas City, KS

Posted 24 January 2012 - 14:16

Yes I would prefer to use a database as well, but these resources are not available for use.

So far the data is broken down a column with each employee grouped by the departments that they work in. I need to track 4 separate metrics each day for a month. Then these totals for each metric is tallied and totaled for a month end sum of each metric. The main concern is how to keep the names of employees update across the sheets. I wasn’t sure if there was some Excel feature that would allow managing this easily.


Posted Image
[/CODE]

#4 Roger H.

Roger H.

    Neowinian Senior

  • Tech Issues Solved: 20
  • Joined: 18-August 01
  • Location: Germany
  • OS: Windows 8.1
  • Phone: Nexus 5

Posted 24 January 2012 - 14:25

Oops, ninja edit :ninja: :p