Database Solution Help

[kylemumma]

Hoping that someone can help me with a database solution.

I am managing a large amount of data for a summer camp. I have a primary sheet with all data (names, ages, heights, camper group names, camper team names, etc). I then have secondary sheets where I have copied say camper names and teams for easy printing/sorting. I do a lot of different sorting of the data to look up different things for parents, campers, etc. I am hoping that there is a way I can conditionally format the sheets copied from the main dataset to automatically update when I update the main data set. For example, if a camper changes groups, I would love to be able to update it on the main sheet and have it automatically update on all subsequent sheets.

Any advice would be greatly appreciated.

[FDibbins]

Hi and welcome to the forum

I think you need to use a VLOOKUP or INDEX/MATCH for this (Conditional Formatting is a means of making cells change their font and appearance)

If you can upload a (cleaned) workbook with a small sample of what you have and what you want, Im sure we can come up with something for you
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[kylemumma]

Here is a very small sampling of data. There are 4 tabs in this example, and knowing that all the data might be sorted differently on each tab it would be extremely helpful for tabs 2-4 to change as data in tab 1 changes.

[kylemumma]

Any help would be greatly appreciated!

[FDibbins]

Ok I see what you mean.

I could probably put something together for you, but here is another option that will eliminate the need for the other sheets.

Make sure all the cells in your header row have something in them (you can put a space in cells you neefd to remain "empty"
Then on the Home tab/Editing (at the end), click Sort & Filter/Filter
Ypu should see a small drop-down arrow at the top of each column.

You can use this as a filter to only show certain categories, or you can use it to sort all columns based on the 1 you select.

Save your file and play around with it, see what you think?

[kylemumma]

So there is no way to set it up where data from sheet one is edited simultaneously with data on other sheets without the data necessarily being in the same row? With the quantity of data it just seems like it would be a lot of filtering to do it the way you suggested and is easier visually to keep things in separate sheets. It just becomes a pain to change someones team on the original sheet and then have to make sure that you also change it on a subsequent team rosters sheet.

[FDibbins]

The only other way I can think of is to use VBA. To do this (once-off)...
1st, in Teams A2, copy this down and across as far as you need...
=IF(Master!A6="","",Master!A6)
Then copy that to the other 2 sheets - adjust the references so theuy match the columns you want

Now....
click on the Master Sheet (anywhere)
click the macro (VBA) recorder - it's the small button directly below the Master tab, and to the right of Ready
It will ask you for a Name, call it something like TeamSort
click on Teams, click A1 (Just so you know exactly where the cursor is)
Perform the sort as you normally would, when finished, click the record button again

Repeat this for each sheet.
On Teams sheet, we now need to ctrate a macro button to do the sorting for you.
On the ribbon, click the Insert tab/click Shapes and select a shape (square is good)
drag it to thwe size you want, right-click abd click Assign macro
click the relevant macro name
Right-click again, select Edit Text, editr as needed
Repeat for the other sheets