+ Reply to Thread
Results 1 to 10 of 10

Create Huge Editable Database in User Friendly Way

  1. #1
    Registered User
    Join Date
    10-06-2021
    Location
    Blandon, Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    14

    Post Create Huge Editable Database in User Friendly Way

    Hi everyone,

    I have been away from Excel for a bit so I'm trying to get my bearings again. I am taking a workbook from someone else and trying to update it. As it currently stands, I will receive an order from my boss and it contains information such as part numbers affected, dates, and various other things.





    Is this layout manageable or should I rethink how I want to present this information?



    I have a "Dashboard" which will show the orders and statuses such as completed, date, notes which I would like to pull from the database sheet.

    I have an "Add" sheet where I can copy/paste the cells from my boss' order sheet. There is a macro button that will copy the data to the database, then clear the add sheet so I can start fresh when he sends me another order.

    I have the database sheet which stores the info from the "Add" sheet. This will just keep building over time.

    The main sheet I'm having issues with and confusing myself over an answer is the "Update" sheet. I would like to type in any order # that is in my database and have it populate all of the part #s and other info, but ALSO edit information there? I am making machining programs and I'd like to type in the order number, see all part#s affected, and set in a drop down box that I have completed, not completed, each program. Is there a way to lookup multiple rows/columns at once AND edit this info? Or should I restructure?


    Hopefully this made any kind of sense. Thank you for reading.

    *PS*
    I am just posting for the first time and it is telling me when I upload my workbook to upload and scroll down to "Go Advanced" but I'm seeing nothing of the sort. If I didn't upload this properly, let me know.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-06-2021
    Location
    Blandon, Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Create Huge Editable Database in User Friendly Way

    I have a new idea after talking to myself through my lunch break.

    I still need help "Looking up" multiple rows at once up to the next blank row.

    As for "editing" the database. I could use the vba to cut the data I want out of the database, paste it to the edit sheet, and then click a button that cuts it and pastes it right back to the database. I don't know how to do that, finding a set of data through vba looking for the order number and subsequent part numbers, but I'm sure it can be done.

    I will look into this for awhile and see what happens.

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

    Re: Create Huge Editable Database in User Friendly Way

    look at the table in your sheet 'Order Database' - use 'proper tables' - do not have merged cells within the data storage/manipulation areas.
    with your tables have every relative cell filled and no empty rows in the table or at the end of the table otherwise you will have to introduce complex searching procedures.
    referring to your second post - set out on a sheet an example how you want the data extracted.
    Attached Files Attached Files
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  4. #4
    Registered User
    Join Date
    10-06-2021
    Location
    Blandon, Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Create Huge Editable Database in User Friendly Way

    Hi Torachan. Thank you for checking this out. The only reason I have the table set that way is due to the formatting that I receive these new orders from another department. Is there anything I can do about that myself besides manually deleting the column?

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

    Re: Create Huge Editable Database in User Friendly Way

    Best advice - re-educate the other department to implement Excel in a stable manner.
    For the unwary merged cells are a disaster in the making - especially if the financial integrity is believed and critical.
    There is ample information on the net from experts, how to NOT use merged cell and proper structures if you intend using Excel as a pseudo database.
    If you intend to use Excel for reports do not use the report template as the data store.

  6. #6
    Registered User
    Join Date
    10-06-2021
    Location
    Blandon, Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Create Huge Editable Database in User Friendly Way

    Torachan,

    I think something is wrong with my browser because I've responded multiple times and I come back and see there is no response to yours. Here is what I've done. I have done everything in vba to get my desired results. I have attached the workbook with the macros in case anyone is interested in what worked for me. I am not done with this workbook by any means, but I'm past the original part that I came here for. Thank you!
    Attached Files Attached Files

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

    Re: Create Huge Editable Database in User Friendly Way

    I have had a brief look at your file and it would appear that you are trying to 'add' to your file and also 'edit' or 'update' existing data.
    I have put together a simple 'userform' to demonstrate how this can be done simply directly into your database without the need for multiple extra sheets.
    To add a new item first press 'Clear' this activates the 'Add' button and prevents entering spurious lines, then fill in data and press 'Add' this places the data into the table.
    The rest should be intuitive - select data either by entering the Part # in the find box or by selecting from the listbox.
    There is no formula on the sheet, all actions are within the userform code.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-06-2021
    Location
    Blandon, Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Create Huge Editable Database in User Friendly Way

    I just got in this morning and am looking at the workbook you attached. I am blown away! This is really cool. I'm looking at everything trying to understand how you did it and think it's great. Is this UserForm a template or did you specify with code the dimensions and amount of text boxes? I love what you have an wonder if I could make one with fewer boxes in a different project.

    I will be looking through this, and I deeply appreciate you looking at this project with me.

  9. #9
    Registered User
    Join Date
    10-06-2021
    Location
    Blandon, Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Create Huge Editable Database in User Friendly Way

    I just found how to create a userform like you did and I see the toolbox that comes up with it! Very cool.

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

    Re: Create Huge Editable Database in User Friendly Way

    thanks for the feed-back, glad to have whetted your appetite to have a go.
    any queries post back on this site.

+ 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. Create Import sheet from User Friendly sheet
    By Radioactrev in forum Excel General
    Replies: 0
    Last Post: 08-11-2021, 02:21 PM
  2. Replies: 10
    Last Post: 12-10-2018, 05:38 AM
  3. Replies: 8
    Last Post: 08-11-2016, 11:07 AM
  4. user friendly box
    By K-Ching in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2014, 07:34 AM
  5. [SOLVED] Small help with user form to create a database
    By zaska in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 07-21-2013, 10:47 PM
  6. Replies: 3
    Last Post: 01-07-2013, 06:40 PM
  7. Create a user-friendly log sheet
    By boom929 in forum Excel General
    Replies: 12
    Last Post: 05-26-2010, 10:12 PM

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