Excel 2010 Populating worksheets from a column in sheet 1 and ensuring changes are reflected

[JasonEnsor]

Hi Guys,

It was a struggle to find the best phrase for the subject so i apologise in advance.

I currently have one spreadsheet that has details for a swimmer, this holds Name, Swim day, swim time, Parent 1 contact details, parent 2 contact details and extra account info. The spreadsheet is getting a little cluttered so what i have considered is trying to split this in to 3 worksheets. Swimmer Details, Parent Contact Details and Account Details. Now i know this would be a great time to use a database however 1) I have been asked to use excel by the customer 2) I am more comftable in excel.

My theory is to have the Swimmer Details Sheet as a master list as swimmer names are unique due to using first, middle and last name, again customer requirement (i would of used a unique id). I want to use the list of Swimmer Names in my Parent Contact worksheet to attach parents to the swimmer and in the Account Details to attach to their Account Details.

What is the best way of ensuring that all swimmers on the master sheet populate on to the other two sheets? I am able to create new rows in vba and switch between the sheets and past the information in, however this seems a little bit of overkill.

Any thoughts on how to approach this? Swimmer Name will always be in Column A and I am looking at disabling sorting A-Z etc.. so the position will pretty much be fixed.

Thanks in advance for any help or tips

J.

 

[SkipVought]

Hi,

I'd use MS Query to get the unique data into each sheet. No VBA required. One time effort, unless I misunderstand your purpose.

Protect any sheet appropriately to restrict certain features. Check HELP on protect.

You have 2 major issues to consider. 1) data structure & maintaining data integrity as you maintain your database, 2) how do you want your application to function? Simple reports? Complex controls to join & filter data in various ways?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Hi Skip,

Thanks for the reply,

The issue is that new Swimmers will be added occasionally which is why i wanted to handle it in code as i would in theory hookup the code to my add swimmers function. It was just a thought i had to try to improve readability on the spreadsheet but i am not going to worry about it too much as the application does pretty much work. Maybe once i have finalised the last few bits i will re-look at it.

Regards

Jason