+ Reply to Thread
Results 1 to 5 of 5

Suggestions for how to format my database

  1. #1
    Registered User
    Join Date
    06-10-2021
    Location
    Helsinki
    MS-Off Ver
    Excel for Mac, Office 365, Version 16.49
    Posts
    2

    Lightbulb Suggestions for how to format my database

    I have a library of videos, and I made a database so that you can easily find a relevant video, but I want to start again and make a new database because it is not the best format, so it is not easy to update (i used merged cells, so editing or removing a "row" of data is really difficult and manual).

    The trouble is, I am not too sure what the best way to layout the data would be.

    Each video has a number of categories of data, such as Language, Subtitles, Industry, Use Case etc.

    And for one video, each of these categories can have numerous entries. e.g. a video can have subtitles in more than one language, it can be in more than one industry, for more than one use case etc.

    The purpsoe of this database is to quickly filter by whatever category to quickly find a video that matches your requirements.

    So you are able to find a video that has:

    Subtitles in English, Swedish and Danish
    Use Cases of Internal Communication, External communication and Support
    Industry etc. etc.

    The original database I created works pretty well for this search and filter function, but as I said, it is not a sustainable format and does not support easy editing.

    I have attached a dummy version of my database, so you can see how I have it now and you can see how it works with filtering the data to display only relevant videos.

    Please suggest how I would layout my database as I need to start building it ASAP!

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,268

    Re: Suggestions for how to format my database

    in your database you need to get rid of ALL merged cells - each row needs to contain a full row of data pertaining to one item.
    investigate the use of 'tables' to store your data using a UserForm interface for data entry - remove all formula from sheet and use VBA code for sorting/filtering.
    just my initial observation.
    torachan.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: Suggestions for how to format my database

    Quote Originally Posted by torachan View Post
    in your database you need to get rid of ALL merged cells - each row needs to contain a full row of data pertaining to one item.
    investigate the use of 'tables' to store your data using a UserForm interface for data entry - remove all formula from sheet and use VBA code for sorting/filtering.
    just my initial observation.
    torachan.
    Agree with most, except the VBA part lol. If a user is already skilled in VBA, then fine, but for new users (heck, even for experienced users), VBA can be a bit daunting
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-10-2021
    Location
    Helsinki
    MS-Off Ver
    Excel for Mac, Office 365, Version 16.49
    Posts
    2

    Re: Suggestions for how to format my database

    Quote Originally Posted by FDibbins View Post
    Agree with most, except the VBA part lol. If a user is already skilled in VBA, then fine, but for new users (heck, even for experienced users), VBA can be a bit daunting
    Grateful for your support haha, I have no clue with VBA. And the people I am making this for, who will be using the database definitely have zero experience with it.

    So any alternative suggestion??

  5. #5
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,268

    Re: Suggestions for how to format my database

    You have already identified what your ideal solution is by naming your file.
    Excel is a very poor 'database' in an app of this nature you should consider the use of 'Access' or similar pure database.
    If you decide to continue with Excel, examine the use of tables, they will aid you in keeping a more structured format.
    You are archiving data - using your sheet as a data store - steer away from making the sheet 'graphically pretty' - (such as merged cells and lots of colours).
    As you say you/your end user have no experience of VBA - has your end user the experience to maintain the expansion of data and expansion of all the formula.
    torachan.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 08-13-2015, 10:24 AM
  2. eliminating format data needs some idea's and suggestions please
    By Pilot5000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2015, 11:07 AM
  3. Database suggestions
    By Soywheat in forum Excel General
    Replies: 1
    Last Post: 03-18-2013, 11:37 PM
  4. date Format in Combobox - format changes when data is transferred to database??
    By sgtbash999 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2012, 06:13 PM
  5. format of cells from database
    By DaveRust in forum Excel General
    Replies: 3
    Last Post: 05-13-2009, 01:09 PM
  6. Format on New Database Query
    By fncuis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-22-2005, 12:05 PM
  7. Wrong format of database
    By Harpagornis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2005, 09:46 AM

Tags for this Thread

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