+ Reply to Thread
Results 1 to 12 of 12

Setting up a Database

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    Newcastle under Lyme, England
    MS-Off Ver
    Excel 2010
    Posts
    20

    Setting up a Database

    Hi all,

    I have a query surrounding databases.

    I want to set up a sheet within Excel where i can select a Student name from a drop down list, that bit i'm fine with. When selected i want another cell, say A2 to display a list of information, it may be several cells, i have yet to decide.

    Now the tricky part (for me anyhow) i want to be able to edit the information in cell a2 if it is wrong or has changed, but for this info to back fill to the original data sheet.

    Any ideas?

    Chris

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Setting up a Database

    yes, use a database rather than excel Relational databases (like access) do this by default

  3. #3
    Registered User
    Join Date
    01-22-2013
    Location
    Newcastle under Lyme, England
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Setting up a Database

    Thats all well and good, but the end users of this a) dont have access on their machines, but have Excel and b) wouldn't know how to use Access, but have a basic understanding of Excel.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Setting up a Database

    You're knocking a round peg into a square hole but I get where you're coming from.

    Conceptually, you need one data sheet with a unique identifier on each row that doesn't change - you need this constant since it will be the id of the user, this id is then used as a reference point - so when you select a user, this is returned also. That allows you to edit all the user's details whilst keeping a constant reference to the row.

    I guess you'd probably then want a macro that knows when a cell in the range has been changed and updates the original record.

    Maybe like the attached - try changing the name in the drop down and the values in G2:G4
    Attached Files Attached Files

  5. #5
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,689

    Re: Setting up a Database

    Here is a tutorial on building an Access database.

    http://www.accessmvp.com/strive4peace/

    Once built, you can set up to run on non-access licensed machines using the Access RunTime program that is free from MS
    Here is a write up on that.
    http://www.hitechcoach.com/

    If this is a direction you wish to go, post back with questions as you move forward. Good Luck with your project.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,689

    Re: Setting up a Database

    self deleted as system posted response twice.

  7. #7
    Registered User
    Join Date
    01-22-2013
    Location
    Newcastle under Lyme, England
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Setting up a Database

    Hi Kyle,

    Thanks for the database that you included it is working a dream.

    I've moved the cells around a little bit so that i can make some sort of form for the user to fill out. I've got one further change that i want make to it and wanted your help on the VBA script.

    On the sheet that allows you to input the data for the write back, is it possible to have three columns? I've moved the input cells to now be C7:C9 instead of G2:G4 in your example. So I want to have three headings Monday, Wednesday and Thursday under which i can enter two lines of info.

    I've tried amending the script to go between C7:E9 but this isn't working properly.

    Can you help?

    chris

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Setting up a Database

    You'd need to attach your workbook so I can have a look - though this:
    Monday, Wednesday and Thursday under which i can enter two lines of info
    Doesn't sound right, you'd only one line of data which relates to one line in your database. You don't want multiple lines in the database per user

  9. #9
    Registered User
    Join Date
    01-22-2013
    Location
    Newcastle under Lyme, England
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Setting up a Database

    Kyle,

    how do i upload the spreadsheet to here?

    Chris

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Setting up a Database

    If you reply > go advanced there's an option to upload attachments

  11. #11
    Registered User
    Join Date
    01-22-2013
    Location
    Newcastle under Lyme, England
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Setting up a Database

    DB Database v2.xlsm

    There you go...

  12. #12
    Registered User
    Join Date
    01-22-2013
    Location
    Newcastle under Lyme, England
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Setting up a Database

    Quote Originally Posted by Kyle123 View Post
    If you reply > go advanced there's an option to upload attachments
    Hi Kyle, did you get chance to have a look at the file i uploaded with the amended script?

    Chris

+ 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