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!!!
Bookmarks