Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Multiple Worksheets For Data Entry. Bad Practice

  1. #1
    Join Date
    8th April 2008
    Posts
    7

    Multiple Worksheets For Data Entry. Bad Practice

    Hello,

    I currently have a spreadsheet that has a total of 20 workbooks in all. Each workbook contains between 5 and 100 rows and between 20-40 columns. Each column is a different category that I have data on for that particular tool manufacturer. What I want to do is create a front workbook where I can do all my searching. I would like the user to impute data into a search cell, hit a macro button, and have the full row that data was in be shown to them. Depending on which criteria they search for, the value may be found at the start, middle or end of the row.

    Also, like I said the number of columns differs between each workbook. I would also like it to print out the categories for that workbook so that the person inputing the data knows what all the different numbers are when they come up.

    Each workbook is a different tool manufacturer that my company has a tool from so there should never be duplicate data. The problem is that after I finish the spreadsheet, I am turning it over to the floor workers and they will have the ability to add/change any information in any workbook. If a mistake is made and different workbooks have the same data, I would like it to print out the category and data from the first workbook, then print the category and data from the next workbook and so on. The Category headers make up the first 2 rows of each sheet.

    My knowledge of Visual basic is very limited so if you are kind enough to help me out, please try to explain everything clearly. I would appreciate any help anyone could give. Thank you very much

    R. Sherman


    edited to give location of category headers

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2005
    Location
    Michigan, USA
    Posts
    844

    Re: Searching And Printing From Multiple Workbooks

    I'm assuming that you mean you have 1 workbook with 20 worksheets...

    Does each worksheet share column headers? (Regardless of the fact that some have more than others)

    If so I'd suggest making one worksheet with all your data in it; see Efficient Spreadsheet Design. I woudl also recommend using a Userform so that you can really control what end users are able to do, in addition to the fact that you can make it "pretty" and overall more user friendly.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    8th April 2008
    Posts
    7

    Re: Searching And Printing From Multiple Workbooks

    Yes you are correct. I am using 1 workbook which has 20 worksheets in it. I am sorry if this caused any confusion for anyone.

    Does each worksheet share column headers? (Regardless of the fact that some have more than others) If so I'd suggest making one worksheet with all your data in it; see Efficient Spreadsheet Design.
    As far as this is concerned, I am not exactly sure what you mean by share column headers. Yes each worksheet have certain categories in common but there are also ones they do not sure. This is why I decided to make multiple worksheets. If I go with a single worksheet, I will end up with multiple columns which are only used by a few rows out of the hundreds in the worksheet. If that is the only way possible to get the search feature that I want to use to work, then that is something I will have to deal with. I hope I interpreted that right. If not, please let me know and I will give you a correct explanation.

    I woudl also recommend using a Userform so that you can really control what end users are able to do, in addition to the fact that you can make it "pretty" and overall more user friendly.
    I am looking into this now. That is along the lines of something I wanted to use for the search feature but can also be used for the later input. Thank you for the suggestion and all your help so far.

    I have been trying to look up ways to search and have it bring the result to the fore front for the user to easily see and understand but I have had no such luck finding something that looked like it did what I wanted that I could understand. If anyone has any help, I would be very grateful.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2005
    Location
    Michigan, USA
    Posts
    844

    Re: Searching And Printing From Multiple Workbooks

    Personally, I always opt to have all data in one sheet. I don't care how "ugly" it is and if all cells contain data for each 'item' or not...it just makes it much simpler when you want to get data out or put data into the database.

    You can go either route. If you keep multiple sheets, within your userform, you'll want to add say a dropdown that lists each sheetname/Manufacturer so you can easily reference it. Keep in mind too of new worksheets needing to be created or existing ones to be deleted.

    For searching you could probably use FIND, or VLOOKUP, etc and have it based on some sort of input to your userform. I'm assuming that every item on every worksheet has some sort of unique identifier.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th April 2008
    Posts
    7

    Re: Searching And Printing From Multiple Workbooks

    I understand what you are saying. I think I am going to move them all to one sheet and have the user just deal with a userform so that it doesn't matter what the sheet looks like.

    I was researching userforms and I came to this website:

    http://www.contextures.com/xlUserFor...ml#ButtonsCode

    That is currently what I am using to help me set up my interface.

    I will go look up and try Find or VLOOKUP and hopefully integrate them with my userform. Thank you very much for all your help in this matter.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Best Practice Layout For Yearly & Monthly Data
    By joeseppy in forum EXCEL HELP
    Replies: 3
    Last Post: May 29th, 2008, 12:02
  2. Multiple User Data Entry Form
    By M40wen in forum EXCEL HELP
    Replies: 6
    Last Post: June 28th, 2007, 21:10
  3. Best Practice - Multiple Workbooks?
    By TeRex82 in forum EXCEL HELP
    Replies: 3
    Last Post: June 29th, 2006, 07:51
  4. Linking Data - Best Practice
    By ihills in forum EXCEL HELP
    Replies: 3
    Last Post: May 13th, 2006, 16:31
  5. Data Entry Sorter to create multiple spreadsheets
    By johnnyboy4ever in forum EXCEL HELP
    Replies: 1
    Last Post: March 28th, 2006, 16:07

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
  •  
porno