Referencing problem

MrStressed · Mar 26th, 2002, 10:01 AM

Afternoon All. I have a real nasty I need a solution for.

A spreadsheet has four sheets, one per region, each of these sheets lists employee id numbers, each employee has an id number for each sales area, some id numbers are the same in different sales areas. This is the sheet I need to populate from the following:

Spreadsheets for each area (twelve areas)listing the employee id number and the number of sales they have achieved for different product types.

How do I get the data from my 'area' sheets to my 'region' sheets ensuring that the correct data referenced to the employee id number goes to the correct 'region' sheet.

You help would be gratefully appreciated

Anne Troy · Mar 26th, 2002, 10:13 AM

IMHO...

First thing you need to do is get all the area sheets into one sheet. Then you get all the region sheets into another sheet. Your region sheet should contain summary information of all your area sheets. Use Autofilter, Grouping, and Subtotals (all under the Data menu) to perform the *separations* of data that you're now performing by using separate sheets.

See my little do-up on file layout for using Excel as a database:

http://www.thewordexpert.com/excel.h...utForDatabases

Let me/us know if you need more help from there.

MrStressed · Mar 28th, 2002, 06:37 AM

I have posted almost the same question again. I followed the link you gave and I have followed the conditions and text of that link. My spreadsheet(s) comply. My second posting, I believe, explains my problem a little clearer. I would like to avoid putting everything into one sheet, as it takes just as long to strip out the data i need to ignore, then to sort it then to reference the employee id to the manager.
Can you suggest anything further?
Regards.

Anne Troy · Mar 28th, 2002, 07:09 AM

Actually, did you try the features I've mentioned? Why would it be more difficult to *strip out* the data? The thing is, you don't have to strip it out. By using autofilter, you can choose to view only the data from a specific branch. Perhaps you need to check into these features further. It seems as though you do not understand how they work. If you'd like to send your file so I can give you an example as to how I see your file working, feel free. Dreamboat@TheWordExpert.com

Mar 26th, 2002, 10:13 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,512	IMHO... First thing you need to do is get all the area sheets into one sheet. Then you get all the region sheets into another sheet. Your region sheet should contain summary information of all your area sheets. Use Autofilter, Grouping, and Subtotals (all under the Data menu) to perform the separations of data that you're now performing by using separate sheets. See my little do-up on file layout for using Excel as a database: http://www.thewordexpert.com/excel.h...utForDatabases Let me/us know if you need more help from there. __________________ ~Anne Troy

Mar 28th, 2002, 07:09 AM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,512	Actually, did you try the features I've mentioned? Why would it be more difficult to strip out the data? The thing is, you don't have to strip it out. By using autofilter, you can choose to view only the data from a specific branch. Perhaps you need to check into these features further. It seems as though you do not understand how they work. If you'd like to send your file so I can give you an example as to how I see your file working, feel free. Dreamboat@TheWordExpert.com __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 10:01 AM	#1
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	Afternoon All. I have a real nasty I need a solution for. A spreadsheet has four sheets, one per region, each of these sheets lists employee id numbers, each employee has an id number for each sales area, some id numbers are the same in different sales areas. This is the sheet I need to populate from the following: Spreadsheets for each area (twelve areas)listing the employee id number and the number of sales they have achieved for different product types. How do I get the data from my 'area' sheets to my 'region' sheets ensuring that the correct data referenced to the employee id number goes to the correct 'region' sheet. You help would be gratefully appreciated

Mar 28th, 2002, 06:37 AM	#3
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	I have posted almost the same question again. I followed the link you gave and I have followed the conditions and text of that link. My spreadsheet(s) comply. My second posting, I believe, explains my problem a little clearer. I would like to avoid putting everything into one sheet, as it takes just as long to strip out the data i need to ignore, then to sort it then to reference the employee id to the manager. Can you suggest anything further? Regards.