Check out some similar questions!

charlie05 · May 2, 2013, 07:14 AM

Hi,

I have data-forms containing employee data in different sheets in an excel workbook, it is set up for each employee to have his own sheet/form. Forms are all the same with the same information variables in the same cells.

I which to include an overview sheet, containing a table that summarizes all information, in order to populate it with data I was thinking of doing something like:
If cell "employee number" (I would manually enter the employee number into that cell, or just build in a list) is equal to a specific sheet with that employee number as a name, then return the information according to the position on that sheet, for each column heading. Then just drag those down, instead of linking each cell individually.

Does anyone know of a way to get this to work? Or perhaps a better way of setting up this info? Most of the info entered on the individual sheets give the employees points, meaning there will be a formula calculating the points and on the main sheet you would see the result.

Thanks,
Charlie

ebaines · May 2, 2013, 07:37 AM

You can use the "indirect" method of addressing cells in other sheets. For example: suppose you have sheets named after employee IDs, let's say one sheet is named "2001" and another is named "2002," and suppose cell A1 in both sheets has data that you want to copy to the summary sheet. You can create a list in column A of the employee nmbers - so that cell A1 is 2001 and cell A2 is 2002. Then in cell B1 you use the formula: =indirect(A1 & "!a1"), and that will put the contents from cell A1 on sheet 2001 into cell B1 of the summary. Copy and paste cell B1 to B2 and it automatically becomes =indirect(A2 & "!A1"), so you get the data from sheet 2002 in cell B2. Hope this helps.

charlie05 · May 2, 2013, 08:55 AM

Thank you very much, ebaines... just got it to work, exactly what I was looking for!

ScottGem · May 2, 2013, 10:03 AM

Frankly, I would NOT do this in Excel. Excel is not the best tool for managing data. Excel is a number cruncher, not a data manager. You would be better off using Microsoft Access (if you have it) or some other database tool.

charlie05 · May 2, 2013, 10:45 AM

Originally Posted by ScottGem

Frankly, I would NOT do this in Excel. Excel is not the best tool for managing data. Excel is a number cruncher, not a data manager. You would be better off using Microsoft Access (if you have it) or some other database tool.

Yes thank you Scott, I have considered or more so feared that as well... problem is I feel pretty much at home in excel as a user, however have never used Access or any other database program... if you could point me towards an easy entry to Access file I would greatly appreciate it.

Thanks again,
Charlie

ScottGem · May 2, 2013, 01:53 PM

Originally Posted by charlie05

Yes thank you Scott, I have considered or moreso feared that as well... problem is I feel pretty much at home in excel as a user, however have never used Access or any other database program... if you could point me towards an easy entry to Access file I would greatly appreciate it.

Thanks again,
Charlie

Access has some templates that can be used to keep track of employees. Depends on what your needs are.