Can this be done in excel?

[thetexan]

We have a flight school with about 600 students. There is a training department, a flight dispatch desk, business and financing office, and a couple of others.

I want ant to create a relational database with excel ( each person is already familiar with excel) with several tables. I need each person to have access to there particular table on their own computer with all of these in communication with the main database.

For example, the business secretary keeps records on each student concerning enrollment, finances, etc.

the flight dispatcher, in another office, keeps track of flight schedules, flights, etc. on his computer. and a couple of other

Can all of these spreadsheet tables be a part of the main database, each communicating from these different offices so that someone can run power pivot tables or queries from an entirely different office?

I would rather do this with excel and not access if possible.

So so how do you interconnect distant spreadsheets together into the master
database so as to allow the administrators to query that database with pivot tables, filtering, etc.

tex

 

[Michael M]

Sounds like one workbook with a number of worksheets, all individually hidden and protected with that users password.
All sheets can then be interacted by the "admin" to either a dashboard or Main Summary Sheet
So when the workbook opens a password is asked for and that passwords only opens that users sheet.

HOWEVER, security in Excel is weak and can easliy be bypassed, so anyone with some Excel skill can snoop around if they wish

 

[offthelip]

Yes , it is possible to do this, I did something similar for a customer a couple of years ago. I did this by using linked workbooks.
The application was to track new clients for my customer they were getting 30 to 60 new clients a month,
My system had a central "Summary and controlling" workbook. This workbook automatically created a new blank worksheet every month and added that months clients to the worksheet.
By default this workbook opened in "READ onlY" mode so any number of people could look at it. When a new client came in the user had to open the summary workbook in read /write mode.
They then clicked on a button to add a client. This button then automatically created a folder on the shared drive for all the clients documents, it created a "client" workbook from a template in the clients folder, The user would fill in the template with the client data
and when the form was complete the vba would put a series of links from the client workbook back to the "summary" workbook . There was one row for each client on that months worksheet in the summary workbook. Also there was a hyperlink in the summary workbook to open the Client workbook on that row. Obviously there was a lot of vba in this system but it worked very well, the customer was delighted and when I last saw it they had well over 1000 clients and linked workbooks and it was all running with no problems at all.. The one problem they did have was when EXCEL crashed when somebody had it open in read /write mode and the user restored it from the autosave version. All the links were corrupted. so I sugggest turning off autosave if you do anything like this.
One of the major advantages of doing it this way was that my customer had 5 or 6 people working in the office they could all update the individual workbooks of the one client they were dealing with and the Summary workbook would update automatically. They could all have the summary workbook open because it was in readonly mode, so they could all look at the data in the summary workbook. there were some issues about the summary workbook not updating instantly but that was not a problem for the customer, the workst case was solved by closing and reopenning the summary workbook

I hope this helps