Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with keeping formulas in place!
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with keeping formulas in place!
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with keeping formulas in place!
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 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping data without losing Formula's | Excel Discussion (Misc queries) | |||
How prevent formulas to get external references/path to current workbook? | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Formulas: Keeping same row/column reference when columns are inser | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |