Creating a relationship between 3 excel files.

[Ghostcode]

Greetings all,

First time member here, but I plan on sticking around. My heart and soul love Excel to the max. I'm experienced with just about everything in Excel, to what degree, varies.

Here is my proposed problem:

I have 3 Excel Spreadsheets.

1. Data entry template spreadsheet (only I have access to)
2. Shared spreadsheet used by 5 people, where daily values for end of day work are input.
3. Shared spreadsheet used by 5 people (another team, different from #2), where daily values for end of day work are input.

----------------------------------------------------------------------------------

1. Data entry template - Each morning I take certain total values from spreadsheet #2 and #3 and add them together to be summed into this spreadsheet #1 - this spreadsheet gets mailed off to management. They don't care about the teams as an individual, they want the total between the two.


2. Shared spreadsheet used by 5 people - The team can be broken down by clients, each co-worker has a client. Every co-worker inputs values for their client at the end of day into an input tab. Rows A3:A38 are labels. Columns G-K are clients. They then input data relevant to each label/client of their own. I take the total of all the clients of that team in a separate column. I assign that column a date, and transfer it to another tab which tracks the totals of the team/all clients for the day. So essentially tab 1 = input tab, tab 2 = totals of all inputs/corresponding date, tab 3 = tracking for that date corresponding to each date. Every day I keep track of the totals, so I manually copy and paste them in to keep record. The issue of using a formula here is the daily input values change each day, if I didn't copy and paste them, the data changes every day.

3. Shared spreadsheet used by 5 people - exactly like team #2, except each worker in the team has a different client.

What I'd like - Basically, I don't want to do anything manually. I would like a spreadsheet for team 1, I want them to input a list of values at the end of day each day relevant to their client. 5 workers in team 1, 5 clients. I then need a sum of those values for team 1.

Team 2 needs the exact same thing, 5 clients, 5 workers. At the end of day each day, they input values under their clients column. I need to sum them for team 2.

I have a spreadsheet management wants, which basically sums team 1 and 2's data. They obviously only care about the big picture. I manually go through this sheet, copy paste, add/subtract etc, each morning, yesterdays end of day totals - team 1 and team 2. I'd like to make this more automated. I can't seem to figure much way around it considering the daily input values change, and if I used a formula, I wouldn't be able to keep track of that specific days inputs. I have to keep a record of every days totals.

----------------------------------------------------------------------------------

Please give me any insight you can think of, we are pretty flexible, being efficient and less man-time I have to spend on these spreadsheets the better. Honestly it doesn't take me that long to sum the two spreadsheets, input values manually on a third spreadsheet and hand it off to management. I just figure there is a way, I don't know about yet, and I'd like to discover it.

Regards,
Ghostcode.

[mahju]

Hi
If you cam upload your sheet it would be better
however if you only want to sum two sheets having same layout
you can use 3d reference

see attached sheet

Regards