Combining multiple data sources

[marvinsae]

I have a sales team of roughly 10 individuals who input data into their own worksheet which includes the product, sales dates, quantity and so on. All of this is stored in one folder on the server. Within this folder, I have another worksheet which pulls data from all these individual worksheets and breaks down and summarises the data.

It just seems pretty tedious to link every column in the summary to each individual worksheet and it seems like there would be a large margin for error, say if the files were moved or deleted.

Is there an easier way?

 

[Retrofit]

I have roughly the same situation, and I am wondering basically the same.

There should be a way, atleast my brain thinks so, by using the Data column, and the importing Data from other sources, and from there somehow import the data into you summary-woorkbook.

I suppose there are different workbooks, and not as you say that everyone has a sheet each in the same workbook?

I am eager to see if you get any response, I will stay put!

//Retrofit

 

[Joe4]

Is there an easier way?

Definitely, but you are not using the best tool for the job.

What you are describing is a database. You could have a split Access database, where each user has their own front-end, and all the data is stored in a centralized back end.

 

[marvinsae]

I was thinking the same, but the users don't have much experience with access. I'm sure it wont be too hard to teach them since I can lay out some simple forms for them.

 

[Joe4]

A well designed Access database is completely form driven. So your users would not need to know a single thing about Access to use it.
I have created many such Access applications myself, and most of the users have no idea that they are actually using Access.

 

[marvinsae]

I have no issues with designing an access db, however the issue here that i ran into is licensing. looks like i may have to stick with excel

 

[Joe4]

Though I have never done it myself, I have seen people do "hybrid" solutions, where users enter all their data into Excel spreadsheets, and those spreadsheets are imported into an Access database every night (so the users do not need Access licenses). What I have seen is that they have some folder that users save their Excel spreadsheets to, and each night, an automated process imports those sheets into Access and then archives the Excel files.

Just a thought to consider...

I have seen people use Excel as a database before, but it gets rather clunky and cumbersome (since that is not what Excel was not really designed to be a multi-user database).