Can this be done in excel?

thetexan

New Member
Joined
Apr 4, 2013
Messages
6
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,119
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,804
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,403
Messages
5,636,080
Members
416,896
Latest member
Hozier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top