+ Reply to Thread
Results 1 to 2 of 2

Links to external workbook Workaround? - Excel Online (Office 365)

  1. #1
    Registered User
    Join Date
    12-11-2016
    Location
    England
    MS-Off Ver
    Office 2016
    Posts
    5

    Links to external workbook Workaround? - Excel Online (Office 365)

    Hi,

    We use a Workbook for recording Vehicle times outbound (Loading Time, Day, Driver, Destination, Vehicle Registration Etc. Etc.)

    The workbook has 1 tab for each day of the week, 1 Spreadsheet for each Week, 4 Spreadsheets for each Period.

    I would like to build a simple 1 tab spreadsheet as a lookup feed for Sunday Commencing dates, All Vehicles Registrations, Trailer ID's, Employees.

    Basically a list for each that feeds the dropdowns on each spreadsheet above (Data Validation)

    How would I go about linking the feed in Office 365 Excel/SharePoint? (excel data validation list from a central source feed spreadsheet stored in the same OneDrive folder)
    Is it possible?

    Is there any workarounds for such a task, would SharePoint Lists help?

    Thanks in advance

    Trunking Log.jpg
    Last edited by djspod; 11-04-2019 at 06:46 PM.

  2. #2
    Registered User
    Join Date
    11-28-2019
    Location
    Cape Town
    MS-Off Ver
    Office 365
    Posts
    1

    Re: Links to external workbook Workaround? - Excel Online (Office 365)

    Hi djspod - it has been a few months since you posted.

    Did you find a solution to this problem?

    I would consider the following approach:
    1. Create a Master Excel Spreadsheet - with the following worksheets "Employee", "Destination", "Vehicle Registration"
    2. Use Master Excel spreadsheet as your master 'database'
    3. Ensure that your Master Excel spreadsheet is kept up-to-date with all the relevant data.

    On a weekly basis,
    1. Copy the data from the Master to the new "Weekly Spreadsheet" into tabs ("Employee", "Destination", "Vehicle Registration") - which can be used for dropdowns.

    The good

    It does not require opening different spreadsheets to allow for dropdowns to appear. As soon as one needs to reference another spreadsheet from within a spreadsheet - things become nightmarish quickly.

    Things to improve

    1. You can certainly look to replace the "Master spreadsheet"with an Access database or a Sharepoint list. (I personally don;t like Sharepoint, but it can be useful)
    2. You can automate the "Copying of data" to new spreadsheets by making use of VBA or C#

    Where the above "falls down" if a new employee is added during the week (or any other drop down data point), the weekly spreadsheet obviously will not have the latest data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1