+ Reply to Thread
Results 1 to 4 of 4

Need help formatting a workbook with a search function!

  1. #1
    Registered User
    Join Date
    04-12-2013
    Location
    canada ontario
    MS-Off Ver
    Excel 2007
    Posts
    6

    Need help formatting a workbook with a search function!

    Hey guys,

    I have a workbook I'm trying to make, and I don't know enough about excel macros and functions in order to create it in the best, easiest, and most efficient manner. I'll try to give the best description I can, but it will be hard and clarification questions may follow. I'll make sure to edit this post to include updates based on clarification questions asked.

    I'll start off listing all the variables then get into the messy stuff.

    I'm using this workbook to keep track of stats for multiple users.
    Let say I have 3 people.

    Example: Dave, Ron, Herald

    Now each person has multiple categories I must keep these stats for. Lets say each person has 3 categories, but these 3 categories are the same for each person.

    Example: Ears, Eyes, Nose

    Now under each category, there may be a sub category. Let's say there's 2 sub categories per category.

    Example: Pencil, Pen

    Now, each sub-category will contain a first, last, and scope value, which has to be unique to that PERSON, for that Sub-Category in that Category.

    Here's an example of some data.

    Dave->Ears->Pencil->First: 0
    Dave->Ears->Pencil->Last: 300
    Dave->Ears->Pencil->Scope: 301 (the 0 is counted as 1)

    Dave->Ears->Pen->First: 1000
    Dave->Ears->Pen->Last: 2100
    Dave->Ears->Pen->Scope: 1101

    Ron->Ears->Pencil->First: 301 (this value will never be anywhere in the scope of Dave->Ears->Pencil as he cannot receive that scope, though no rules must be created as it will just never happen).
    Ron->Ears->Pencil->Last: 1000
    Ron->Ears->Pencil->Scope: 700

    Ron->Ears->Pen->First: 0 (this value will never be anywhere in the scope of Dave->Ears->Pen as he cannot receive that scope, though no rules must be created as it will just never happen).
    Ron->Ears->Pen->Last: 999
    Ron->Ears->Pen->Scope: 1000

    I'm sure you follow here, Herald's values will never overlap with the above two.

    Now, I'm going to be inputting stats like these daily, and these scopes will constantly be different each day, yet remaining unique (the numbers will get into the billions eventually).

    I need a way to keep this data, with a possibility of a larger number of people (lets say 20-100), with a larger number of categories (<10), and a larger number of sub categories (<10).
    On top of all of this, I need to be able to search the workbook for a number that would fall within the scope of the data. However, since the scopes can be similar in different categories and sub-categories, I need to be able to search giving the category and sub-category I'm looking for, and just list a number. I need the workbook to calculate based on the first, last, and scope values, to find which person this stat belongs to.

    A big issue is, since the numbers are unique and will be broken up, one person's scopes for one category could look like this

    Dave->Ears->Pencil->First: 0
    Dave->Ears->Pencil->Last: 300
    Dave->Ears->Pencil->Scope: 301
    Dave->Ears->Pencil->First: 1500
    Dave->Ears->Pencil->Last: 3000
    Dave->Ears->Pencil->Scope: 1501
    Dave->Ears->Pencil->First: 10000
    Dave->Ears->Pencil->Last: 11000
    Dave->Ears->Pencil->Scope: 1001
    Dave->Ears->Pencil->First: 25000
    Dave->Ears->Pencil->Last: 28000
    Dave->Ears->Pencil->Scope: 3001
    Dave->Ears->Pencil->First: 100000
    Dave->Ears->Pencil->Last: 150000
    Dave->Ears->Pencil->Scope: 50001

    And remember, each person is going to have similar values, and multiple categories.

    Any ideas? tips?

    Edit:

    I uploaded an example for some sample data.

    I wasn't sure how to organize and create the workbook in the most effective manner, so I just showed what I was maybe thinking of.

    I gave each person their own sheet in the workbook. Each person's sheet will look exactly the same. I figured I would go downwards for each category, and inset new cells as needed each day for the input. (Maybe a button could come in handy that would add data for you).

    Note that each person doesn't have each other's numbers for the same category->sub-category and that they actually cover the full range of numbers from 0 - current. each person get's a piece though.

    0-300 is dave, 301-700 is ron. etc.

    Also please note the way the data is stored, and I need to be able to search for a specific number in the scope of a specific category->sub-category. If that makes sense...

    Thanks a lot!!!
    Attached Files Attached Files
    Last edited by winbawt; 04-12-2013 at 07:38 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Tough workbook here. HELP!

    Hi and welcome to the forum

    We would love to help you with your question, but 1st, in accordance with forum rule, please rename your thread to something more meaningful, that actually describes your problem.

    Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

    Also, many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertese, they might only open those threads.

    Look at it this way...if you typed that title into google, what would you expect to get back?
    To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title


    Also note it will be easier to see what you want, if you upload a sample workbook (no sensitive info), show the data you are working with, a few examples of your expected outcome, and how you arrived atthem :0

    Thanks
    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

  3. #3
    Registered User
    Join Date
    04-12-2013
    Location
    canada ontario
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need help formatting a workbook with a search function!

    Hey FDibbins:

    Thanks for the reply. I tried changing the title, but I'm relatively new to excel. I have a programmer's logic, but no programming knowledge. The reason I didn't include a workbook is because I have no idea how to construct the workbook to be effective. That was one of the reasons for the thread. I was looking for some insight on how I should construct it. I made an example to show what I was maybe thinking of.

    I'll upload the workbook and put a little description in the first post.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help formatting a workbook with a search function!

    Like they say, the way to eat an eliphant...is by taking small bites. So, lets start off by getting you to put something together.

    Start off by having a table that will contain your data

    then, put something together manually that kinda sorta looks like what you want your answers to be, (and how you arrived at that), and we can start figuring out how to get those answers for you

+ 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