Help with keeping formulas in place!

[tapuza]

Hi Hi!

I need some rookie help!

My situation:

I run a youth group, and need to keep track of several hundred kids.
know Access is the solution to all my problems, but as an interi
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 hav
excel to keep track of the data divided by age groups (one of th
parameters on the first sheet)

I tried to use the = sign in the formula field and suggest that row
in sheet 2 = row 3 in sheet 1 for example. That didn't work because th
moment I sort the first list differently, the relationship is maintaine
with that specific row, and not the specific record.

Help please!! :

 

[Guest]

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
(e-mail address removed)

 

[Dave Peterson]

I think you'd be better served to keep all your data in one worksheet (well, if
you stay with excel).

Then apply data|filter|autofilter to show/hide what you want hidden and shown.

If you really do want separate sheets for each grade, you can use a macro to
delete the secondary sheets and recreate them each time the data changes. That
means that each change goes to the total combined sheet--any change to the
individual grade sheet is essentially a waste of time.

Debra Dalgleish and Ron de Bruin have samples that you may like. Ron's addin
may be sufficient right out of the box.

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

And Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm