Forum Discussion
Copy cell from an array of sheets.
I have 4 sheets. “Jane Doe, John Smith, Keven Brown, and Jean Grey” Each of these sheets is the same and contains information associated with an employee.
I want to be able to populate the Current Active Employee sheet columns D-Q with the information from each of the sheets based on, if the employee number matches/is the same as the corresponding cell in Sheet 1 Column A.
I have inserted this equation on ‘Current Active Employees’!D1-Q7, but have not gotten any results.
The formula I am attempting to use is:
=IFERROR(INDEX('Jane Doe:Jean Grey'!$A$13,MATCH([@[Employee Number]],'Jane Doe:Jean Grey'!$B$2,0)),"")
Any suggestions?
For a number of years before I retired, I was the director of the human resources database system of a major corporation, so I'm responding from that background as much as I am from my current status as a retiree who enjoys helping people use Excel more efficiently.
For starters, I'm going to offer the observation that surely in the real world you don't have only four employees. Which leads to the comment that you might be better served (let me make that stronger: you would be better served) by a different design.
What exactly that design would be depends on the bigger picture: what is the purpose of this workbook? Are you solely concerned, for example, with current sales assignments? Would you want to track history of appointments over time? Jane Doe began her sales career on date mm/dd/yyyy in XXX, moved on mm/dd/yyyy to position ZZZZ, etc? Pay rates?
In other words, what are you tracking and how are you going to use it?
In general, Excel works better from a single database, or single data table, for storing history of all employees in a given population (which could be the entire corporation). There may be multiple tables--one might keep family history (for benefits admin) separate from job/location data, for example. But comparable data on all employees is more ideally stored in a single core spreadsheet,.not as a separate sheet for each employee and then a consolidating sheet that pulls. Use Excel to extract current status of any given employee (or group) from that single database.
Anyway, I ask this question because it's what I would ask if we were sitting down face-to-face. I would want to make sure we're not designing something that we'd regret later. It's possible that your approach--as you've started out--is valid for your need, but I'd want to be sure that you're articulated that need fully before going to much further. Feel free to ignore the questions; they're meant to help, not to frustrate or annoy.
