Using only formulas there's a solution. It might be a bit more involved than
what you want to do. Let me see if I can describe it for you.
Establish 2 interum spreadsheets called 'sort1' and 'sort2'
The purpose of sort 1 is to assign a numerical value based upon grade to
each one of your kids. In order for this to work each grade will have to be
entered the same way. I'd suggest for your 'grade' field that you use a drop
down list. The result is that all of the fourth graders would be numbered
400-499, thrid graders 300-399 etc. If you had more than 100 kids in each
grade then you could base your number value system on thousands instead.
Sort2 will allow you to change any formatting necessary before you put them
into their own sheets.
To get the data into it's own sheet all you have to do is a Vlookup that
references sheet2 and that counts as it goes.
The formulas will increase the size of the document but you will have a
dynamic workbook that may open slow but operate quickly. No matter how you
sort original data on the input or master sheet it will always break them
down the way you want them too. If you alphabetize your master sheet, each
grade sheet will be alphabetized. If you sort them by age each grade sheet
will be sorted by age. etc.
I didn't include examples on the formulas because they need to be specific
for how you have your sheets labeled. It sounds like a complicated project
but you'll be able to cut and paste the formulas as you go and this will save
some time. Using the "$" in the right places will help make certain
references absolute and increase productivity.
Access is still the right program. Excel will work as an interum.
I usually ask folks to send me the files and let me set them up. The
problem this time is your spreadsheet has information about kids on it. If
you feel comfortable send it my way and I'll see what I can do.
I do believe I'm about as far away from your kids as anyone can get. I'm a
soldier in Iraq and we've got some young folks in the army but no fourth
graders.
Good Luck!
-Jacob F. Roecker
"tapuza" wrote:
Hi Hi!
I need some rookie help!
My situation:
I run a youth group, and need to keep track of several hundred kids. I
know Access is the solution to all my problems, but as an interim
solution am trying to figure out a way to do it in excel.
I have one workspace with several sheets.
sheet 1 - list of all participants and their contact information
sheet 2 - copy of all 3rd graders contacts
sheet 3 - copy of all 4th graders contacts
etc...
Goal: I need, while maintain full usability of the first "control"
sheet (sorting, adding new enteries, updating existing etc..), to have
excel to keep track of the data divided by age groups (one of the
parameters on the first sheet)
I tried to use the = sign in the formula field and suggest that row 1
in sheet 2 = row 3 in sheet 1 for example. That didn't work because the
moment I sort the first list differently, the relationship is maintained
with that specific row, and not the specific record.
Help please!!
:(
--
tapuza
------------------------------------------------------------------------
tapuza's Profile: http://www.excelforum.com/member.php...o&userid=29864
View this thread: http://www.excelforum.com/showthread...hreadid=495693