Uh Oh! This one might be tough, or I am...

[binky]

Okay, first of all I haven't used Excel very much so please forgive me if this actually is easy.

Situation:
I have 5 sales reps who will each have a workbook with 12 worksheets. (one for each month) What I need to be able to do is have a separate workbook('Master') that is a list of all the entries for each rep, for each month. (it too would obviously have 12 worksheets) This isn't hard to do with cut and paste but...I need to it be dynamic. That is any new, deleted, or changed entries need to be reflected on the 'Master' list. Once I have this Master list I will be linking Access tables to each Master worksheet.

I could do this entirely in Access but the sales reps REALLY like excel spreadsheets.

Any suggestions?

Thanks in advance
Ken :

 

[dianemarie]

Hi Ken, are you familiar with linking workbooks? You can have your master workbook read any or all cells that are in the other 5 workbooks, and when changes are made to any of the first 5, when you open the linked workbook, you can choose to update the master workbook or not. You can also summarize the data in the first 5 workbooks with a formula (ie, one cell might add the total sales for March for all 5 sales reps). You can link to other cells on the same worksheet, or to cells in the same workbook but on a different worksheet, or to cells in an entirely separate workbook(s). Linking starts as a formula in a cell. It may take some initial time to set your master spreadsheet up the way you want, but it beats rekeying data. Help has very good step by step instructions, just key in Linking.