Auto Populate from a Master Worksheet

**Kombat_Ken** · 10-14-2013, 10:20 PM

Greetings Brains Trust, I have tried to find the solution to this in the forums, but either I am blind or not as intelligent as my mother tells me I am.
I have been assigned to manage the data for a number of teams in a charity competition. I would like to add new users and their information, and then for that information to auto populate to each of the teams sheets. I would also like to just update the amounts raised by individuals and have that info updated on the team sheets too. I will then have a workseet with the teams listed by amount raised and the top 10 indivdual earners. I hope this makes sense.

Any and all help is more than gratefully appreciated

**FDibbins** · 10-14-2013, 11:00 PM

Hi and welcome to the forum

This sounds entirely do-able, but so that we can see what you/we are dealing with, I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
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.

**Kombat_Ken** · 10-15-2013, 12:50 AM

Workbook4.xls
Wow, thankyou for the the prompt response. I have (hopefully) attached the basic concept - (I have done this at home on Excel 2010 but at work I use 2003 if that affects things)

**FDibbins** · 10-15-2013, 02:27 AM

Sorry for the delay in replying.

If I understand you correctly, follow these steps...
1. in Master, use a helper column with this copied down...
=D2&COUNTIF($D$2:D2,D2)
(I used G, you can use whatever you like - and even hide it if you want - just adjust the next formulas as needed)

2. use this in all sheets copied across and down all columns EXCEPT the team column and all rows...
=INDEX(Master!$B$1:$F$8,MATCH($C3&ROW($A1),Master!$G$1:$G$8,0),MATCH(A$2,Master!$B$1:$F$1,0))

3. If you plan on typing the sheet name into the team mane column, thats fine, else you can use this...
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1),1))
=INDEX(Master!$B$1:$F$8,MATCH($C3&ROW($A1),Master!$G$1:$G$8,0),MATCH(A$2,Master!$B$1:$F$1,0))