Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design Advice (2003 SP2)

    Good Afternoon

    I am on the 'cadge' for a bit of advice before I attempt to set up a workbook (we do not have Access because even with my limited knowledge I could adapt the template DB that they have). On a free basis we supply empty specialist boxes and packing materials to hospitals in about 35 worldwide locations, in total there are about 50 different types of packing material and each hospital receives a different combination depending on what medical condition they are researching.

    My warehouse guys rely on 'memory' to know what stock is where in the world at any one time so I thought I would have a go at designing something in Excel which would record Stock on hand, stock shipped (what type of stock and to which Country) with a summary sheet so that we could see at a glance who was running short of what.

    I have started a few times with various designs and then could see problems ahead and I just can't seem to get my head around where to start, my latest attempt is

    Having a page for every Country with every product in its own column, I then thought that I could try and design a UserForm that I could update the stock on hand from, for example Country (from range on hidden sheet), type of product (again from Range) and Quantity I was sending them but I would then have a problem of when they send boxes back containing samples where I will have to deduct their stock on hand I may send them 50 boxes and they might use 10 in Jan, 8 in Feb, 11 in Mar it is never consistent. A summary page with perhaps all box types as columns with all Countries in one row could then record the information from the various other pages.

    I just can't see the wood for the trees, if somebody could nudge me in the right direction I will try to create it and post back for advice as and when I encounter problems, what I don't want to do is badly design it to start with so that it will be useless in a couple of weeks.

    TIA cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Design Advice (2003 SP2)

    I am not a fan of creating multiple sheets with the same layout. I would suggest iinstead keeping them all toghether on 1 sheet, adding a column for the "sheet disctincion" (ie Country).

    This allows suing autofiltering and pivot table to summarize the results without a lot of work of coding.

    You could do something simple like a line for a transaction: date, country, quantity (positive to add inventory, negative for sales). A pivot table could summarize the total (plus and negative) to give you current inventory.

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design Advice (2003 SP2)

    Hi Steve,

    My advice might sound pretty obvious, but the source of any data analysis starts with a list, a well designed list!
    Firstly, a list must meet two standards:
    1. It must be a solid block of data, ie. No entirely blank rows or columns running through it!
    2. It must have column labels in the first row of the list.

    I do not know how long your list will become because as you are using 2003 you will only have 65536 rows to work with. But hopefully if this is sufficient, I will definitely recommend to keep all your data on one sheet. In other words put all field info together...country, region, medical product, price, location, etc... In Access, you have the luxury of working with relationships and single topic tables, but in Excel, all data analysis tools work best and easiest if the data source if found at one location as a solid uniform list.

    From this one list you can do any analysis you need...in particular : Pivot Tables for reporting and analyzing, but also individual sub-totaling, sorting and filtering.

    Keeping one main list comprised of all data you want to work with also facilitates import and export procedures and automation using macros or VBA.

    Bottom Line:
    One uniform list (no matter how many columns/rows) that can fit one sheet. If anything, this is the best start you can give yourself if you REALLY do not have chance to go to Access. You can always upgrade to Excel 2007 later if you somehow need 1048576 rows. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design Advice (2003 SP2)

    Thanks Steve and Rudi

    I have tried to follow you suggestions in laying out the workbook and then tried to do the Pivot Table thingy but the Excel help is as illuminating as ancient Greek, not wanting to seem a complete numpty and having to ask again I have trawled the internet for help but none of it makes any sense.

    I have then tried to start making user forms because it is easier to look at and understand than all those buttons that filtering puts up but before I can even get to the 'updating' stage I am being knocked back by the Combo boxes, no matter what I try I can't get them to see the ranges, the attached is my gazillionith attempt tonight to just get the combos to show the ranges, there must be an easy and stadard way to do this.

    If anybody has a mind to can they have a quick look at the attached and tell me why I can't get the combos to see the ranges.

    As usual thnaks in advance

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design Advice (2003 SP2)

    You're populating the combo boxes in the Change event of the combo boxes themselves. That makes no sense.
    You should populate the first combo box in the Initialize event of the userform (i.e. when the userform is opened).
    If the contents of the second combo box depend on the item selected in the first combo box, you should populate the second combo box in the Change event of the first one.
    Otherwise you can populate it in the Initialize event of the form, just like the first one.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design Advice (2003 SP2)

    Thanks for the pointer Hans

    I now have

    Private Sub UserForm_Initialize()
    CboCountry.List = Range("Countries").Value
    cboStock.List = Range("PackingTypes").Value
    End Sub

    and both Combo's display the required ranges, out of interest, for design purposes is it correct to have them in the same initialize event or should you split them

    Private Sub UserForm_Initialize()
    CboCountry1.List = Range("Countries").Value
    End Sub
    ------------------------------------------------------------------
    Private Sub UserForm_Initialize()
    cboStock1.List = Range("PackingTypes").Value
    End Sub

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design Advice (2003 SP2)

    You should *not* split the procedure. A form can have only one Form_Initialize procedure.
    In general, you can't have two procedures with the same name in the same module.

Posting Permissions

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