can we define data types in ms excel?

Question

Hi!

I have run into a problem .. I need to build up some 20 sheets in MS Excel. each of the sheets has records for different type of poeple like worker, sales persons, foreman , manager etc. and then I need to show a summery of those 20 sheets on one page which should state that we have this much total workers in all 20 sheets, this much sales persons and managers etc.

what is the quickest way to do this . can we define custom types like mark some cells as manager or worker and ask excel to sum up all the cell marked as manager or worker.

any tips

the sheets are very lengthy and there are almost 20 ov them thats why i don't want to do it manually.

Answer 1

Stay away from my computer.

Answer 2

the best way to tackle this situation is by using MS Access instead of Excel. With Access you, can create tables that would contain information about the employee and another table that contains information such as position or lets say, the branch they are located. Once you create the tables, you can just create a query that will summarized how many of each positions you have in the company which you can use to create a report.

Answer 3

Though this can be done in microsoft excel, it's very complicated and one wrong # or letter can error the whole thing. If you have the program I recomend using Minitab. It is very similar to excel but much more advanced when it comes to data analysis.

Answer 4

The trick is to apply a named selection for the source, as a substitute of the cellular reference of the different sheet. to call a style: >pick the selection >sort a acceptance interior the call container (interior the toolbar close to the perfect left), or circulate to: Insert>call>define and upload the call there as a substitute. Then, interior the suggestion validation window, for source, you will possibly write: (as an occasion) =myname, as a substitute of =Sheet2$A$a million:$A$10

Answer 5

Well there are several ways to accomplish this. You can mark some fields (a column header = field) as employee type and fill that field with the proper type. You must make sure you are consistent in the spelling of each type = manager and manger will sort out as two separate employee types. You can then use MS query to link the sheets (data tables) and pull data from each sheet into a pivot table and summarize all your fields.

Or

you can import the sheets into access and pretty much accomplish the same thing by linking the tables and querying out the data and writing a report or exporting the query into excel and running a pivot report.

Or

I copied the following from the help files:

Consolidate data in multiple worksheets

Examine your data and decide whether to consolidate it with 3-D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.) in formulas, by position, or by category.
Formulas You can use 3-D references in formulas for any type or arrangement of data. This is the recommended method.

Position If you're planning to combine data that's in the same cell in each of several ranges, you can consolidate by position.

Category If you have several ranges with different layouts, and you're planning to combine data from rows or columns that have matching labels, you can consolidate by category.

Do one of the following:
Consolidate the data with 3-D references or formulas

On the consolidation worksheet, copy or enter the labels you want for the consolidated data.
Click a cell that you want to contain consolidated data.
Type a formula that includes references to the source cells on each worksheet that contains data you want to consolidate.
For example, to combine the data in cell B3 from worksheets Sheet 2 through Sheet 7 inclusive, you could type =SUM(Sheet2:Sheet7!B3). If the data to consolidate is in different cells on different worksheets, enter a formula such as this: =SUM(Sheet3!B4, Sheet4!A7, Sheet5!C5). To enter a reference such as Sheet3!B4 in a formula without typing, type the formula up to the point where you need the reference, click the worksheet tab, and then click the cell.

Consolidate by position or category

Set up the data to be consolidated.
How?

Make sure each range of data is in list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
Put each range on a separate worksheet. Don't put any of the ranges on the worksheet where you plan to put the consolidation.
If you're consolidating by position, make sure each range has the same layout.
If you're consolidating by category, make sure the labels for columns or rows that you want to combine have identical spelling and capitalization.

Name each range: select the entire range, point to Name on the Insert menu, click Define, and type a name for the range.
Click the upper-left cell of the area where you want the consolidated data to appear.
On the Data menu, click Consolidate.
In the Function box, click the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) you want Microsoft Excel to use to consolidate the data.
Click the Reference box, click the sheet tab of the first range to consolidate, type the name you gave the range, and then click Add. Repeat this step for each range.
If you want to update the consolidation table automatically whenever data in any of the source ranges changes, and you're sure you won't want to include different or additional ranges in the consolidation later on, select the Create links to source data check box.
If you're consolidating by position, leave the boxes under Use labels in blank. Microsoft Excel does not copy the row or column labels in the source ranges to the consolidation. If you want labels for the consolidated data, copy them from one of the source ranges or enter them manually.
If you're consolidating by category, select the check boxes under Use labels in that indicate where the labels are located in the source ranges: either the top row, the left column, or both. Any labels that don't match up with labels in the other source areas result in separate rows or columns in the consolidation.

Note Other ways to consolidate data include creating a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) from multiple consolidation ranges.