I'm looking for help on how link a master sheet to populate another sheets in the workbook

[Coly88]

I need help.



I run a baseball league and use basic excel stuff to track stats, rosters, etc. I’m trying something to make life easier. Can I use a master sheet with data and link that data to go into multiple sheets just by? Each team in the league has a tab for its players and salary. I wanted to use a master sheet with all the players and the players salary and link that to all the 24 team tabs. So if a trade occurs I could go to the master sheet with all the players just switch the teams in that sheet and it will then automatically swap those players in the individual team tabs. Is this possible?

I'm attaching a sheet with 3 tabs, although the sheet I normally use will have more. The first 2 tabs are for "hitting" and "pitcher". Those are the 2 master sheets I want to link to the other 2 tabs "SCR" and "FGB". In those to latter tabs "SCR" and "FGB" you'll see spots for "Name Pitchers", which will be linked to the "pitcher" master sheet and "Name batters", which will be linked to "hitting" master tab. My goal is to be able to go into those 2 tabs, enter a team name abbreviation in the column "ILSBC Team" and that player then jumps from one team to one of the other teams.

In the "pitcher" tab the first guy listed is Fernando Abad. Next to his name there's 3 columns (ILSBC team, Salary, Years). I want to put "FGB" in the "ILSBC Team" column and he'll pop up in the team tab "FGB" or any other team for the matter.

[mehmetcik]

Easily.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glenn Kennedy]

Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

Since you are a new or very infrequent user of this site, on this occasion, I will change it for you - I can't right now, as I haven't got a clue what you want.

However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

*Other search engines are available!!!

Regards,

Glenn.

[Coly88]

Is this more acceptable?

[AliGW]

Thank you for your thread title change. Now, please wait patiently for help and don't open any more duplicate threads.

Whilst you are waiting, please familiarise yourself with our rules and expectations: https://www.excelforum.com/forum-rul...rum-rules.html

Thank you.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[Coly88]

I really love you people. You're quick, like 5 minutes after I posted it quick, to tell me I can't make a duplicate post but yet you fail to help me with the answer to my original question. That's great hall monitor skills

[AliGW]

You are posting on a free forum, but we have rules and you have to abide by them. If you don't want to abide by them, then please don't post here.

Because the help you get here is for free, you will get help if and when someone feels willing and able to help you based on the description in your opening post and any workbook you provide. If you have failed to get help so far, it could be that your request is not clear.

If you are in need of urgent help, then you are probably in the wrong place.

Thank you for your patience and co-operation.

[DJunqueira]

I tried to make some improvements in your worksheet, lets see...

[Glenn Kennedy]

Calm down!!

1. Ensure that column headers are the SAME in both sheets.

2. In FGB, A9, copied across and dnown:

=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/(Pitcher!$A$2:$A$1200="FGB"),ROWS($A$9:$A9)),MATCH(FGB!A$8,Pitcher!$1:$1,0)),"")

see sheet.

For other teams, change the bits in red appropriately

[Coly88]

Thank you. I'll patiently wait for one of these fine excel experts to help

[AliGW]

You don't need to wait. See posts #8 and #9.

As Glenn said, calm down.

Please update your forum profile as requested in my earlier post. Thanks.

[Coly88]

Thanks, Glenn. Now what If wanted to add more teams as options other then "FGB"? Can i then put:

=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/(Pitcher!$A$2:$A$1200="FGB","SCR"),ROWS($A$9:$A9)),MATCH(FGB!,SCR!A$8,Pitcher!$1:$1,0)),"")

[AliGW]

Untested, but try this:

=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/((Pitcher!$A$2:$A$1200="FGB")+(Pitcher!$A$2:$A$1200="SCR")),ROWS($A$9:$A9)),MATCH(FGB!,SCR!A$8,Pitcher!$1:$1,0)),"")

EDIT: Sorry, no - this won't work. Ignore!

[Glenn Kennedy]

Not sure if I understand you. I have populated the SCR sheet to show what I think you mean.

If you mean "have a sheet with both FGB and SCR on it" say so, as that's a subtly different Q, AND WILL NEED A DIFFERENT APPROACH.

For SCR, this:

=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/(Pitcher!$A$2:$A$1200="FGB"),ROWS($A$9:$A9)),MATCH(FGB!A$8,Pitcher!$1:$1,0)),"")

changes to:

=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/(Pitcher!$A$2:$A$1200="SCR"),ROWS($A$9:$A9)),MATCH(SCR!A$8,Pitcher!$1:$1,0)),"")

[Coly88]

I see what your saying. The sheet I use will have 24 different tabs not just the 2 in this example. I will attach it here. On each of these 24 sheets I split "pitcher" and "hitting" so i'd need the same formula for both. Also, when I went to the "pitcher" tab and put "FGB" in that player shows up on the "FGB" tab as does "years" but "salary" doesn't. I really do appreciate the help here.

[Coly88]

My file is too big to send... dam it

[Glenn Kennedy]

You're having difficulty uploading your file!! Coffee time??

[Coly88]

It's too big...I added more tabs

[Glenn Kennedy]

Save it as an .xlsb

[Coly88]

here you go... thank you

[Glenn Kennedy]

Time for some baby steps, as I'm lost, now!!

Your raw data is on 2 sheets... Hitting & Pitcher. Yes? No??

If you assign person 1 on the hitting sheet to FGB and person 7 on the pitching sheet to FGB, both names should appear on the FGB sheet. Is that what you wanted?

If not, explain to me as if I was a reasonably intelligent 4-year old.

[JohnTopley]

Try

in FGB

=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/((Pitcher!$A$2:$A$1200="FGB")+(Hitting!$A$2:$A$1200="FGB")),ROWS($A$9:$A9)),MATCH(FGB!A$8,Pitcher!$1:$1,0)),"")

[Coly88]

LOL... smart 4 year old... Ok. A baseball team has hitters and pitchers. So, yes, the raw data is hitting and pitcher. On each tab you'll see a row with pitchers name, salary, years, etc and about 20 rows down you'll see the same but for batters. I need to be able to put the abbreviation for each teams name ( could be one of those 24 team) in that page where it says "ILSBC Team" that player shows up on the team I put in there's tab. I need to do that same thing for "pitchers" and "hitting"

[Coly88]

It's hard to explain on paper. Easier verbally..lol

[Coly88]

It didn't, unfortunately

[Coly88]

I wanna to go to the main raw data sheets (pitching or hitting depending on if they're a pitcher or hitter) put the team abbreviation of one of the 24 teams i have in tabs, and that players then pops up on the roster of choice

[Coly88]

There's a roster of players both pitchers and batters. There's 24 teams in the league and there's a tab for each of the 24 teams. I wanted to go into those main sheets ( hitting and pitching) and disperse those players to the correct teams from the main sheets

[JohnTopley]

See post #22 and attached

[Coly88]

Thanks for trying everyone but nobody seems to understand what I need.

[JohnTopley]

What is wrong with file posted which selects from both "Hitting" and "Pitcher" sheets and places them in "FGB" ??

[Coly88]

It looks like the formula is strictly to "FGB". If I put in " ABD" nothing happens. I need to like all 24 teams. So if i traded the player and changed "FGB" to "ABD" he then goes from one team to the other.

[Glenn Kennedy]

it's probably because your sheet is massive and you have not shown us expected results. I have cut the teams sheets back to 2.

ABD Pitchers, in A9, copied across and dnown.
=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/(Pitcher!$A$2:$A$1200="ABD"),ROWS($A$9:$A9)),MATCH(ABD!A$8,Pitcher!$1:$1,0)),"")

ABD batters, in A24, copied across and down:
=IFERROR(INDEX(Hitting!$1:$1048576,AGGREGATE(15,6,ROW(Hitting!$A$2:$A$1200)/(Hitting!$A$2:$A$1200="ABD"),ROWS($A$24:$A24)),MATCH(ABD!A$23,Hitting!$1:$1,0)),"")

The corresponding formulae for BER look like:

=IFERROR(INDEX(Pitcher!$1:$1048576,AGGREGATE(15,6,ROW(Pitcher!$A$2:$A$1200)/(Pitcher!$A$2:$A$1200="BER"),ROWS($A$9:$A9)),MATCH(BER!A$8,Pitcher!$1:$1,0)),"")

and

=IFERROR(INDEX(Hitting!$1:$1048576,AGGREGATE(15,6,ROW(Hitting!$A$2:$A$1200)/(Hitting!$A$2:$A$1200="BER"),ROWS($A$24:$A24)),MATCH(BER!A$23,Hitting!$1:$1,0)),"")

See file.

[JohnTopley]

Perhaps changing the formulae in each sheet to reference the appropriate sheet might help: all formulae reference "FGB" so no wonder nothing changes..

This was pointed out to you in post #14 from Glenn!

See attached for ABD (Jason Adams)

[DJunqueira]

I think I finally understood what you want, lets see...

[Coly88]

DJ,

Your kind of close. I just wanna transfer players, players salary, and players years from the 2 master tabs to team tabs just by change the abbrevation on the master sheets. One stop transfer without having to do is across multiple team tabs

[wyowhite]

cooly88, I am working on your original wookbook. The attached seems to work, For the pither name lookup use the following in A9 pull down to A29.

=IFERROR(INDEX(Pitcher!$D$2:$D$1154,SMALL(IF(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)=Pitcher!$A$2:$A$1154, ROW($A$2:$A$1154)-ROW($A$2)+1), ROW(1:1))),"")
Must be entered with ctl;shift;enter It is an array formula.
The code MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) is to get the current sheet name for the lookup.

for the hitters use
=IFERROR(INDEX(Hitting!$E$2:$E$1724,SMALL(IF(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)=Hitting!$A$2:$A$1724, ROW($A$2:$A$1724)-ROW($A$2)+1), ROW(1:1))),"")
same enter with ctl;shift;enter.
To fill the columns accross use this in D9 and pull across and down.
Pitchers =IFERROR(VLOOKUP($A9,Pitcher!$D$1:H$1371,COLUMN(C1),FALSE),"")
Hitters =IFERROR(VLOOKUP($A33,Hitting!$E$2:$Z$1824,COLUMN(C1),FALSE),"")
It is really just PURPLE SMOKE & MIRRORS.

[Coly88]

wyowhite,


Thank you. I'll wait until you're done but seems like you're on to it now

[Glenn Kennedy]

Did you look at Post 32? Am I miles out??

[wyowhite]

This is the file that failed to load

[wyowhite]

i Just put in dynamic sheet name can copy this sheet for new one and all is well??

[Coly88]

Glenn,


You got it! I missed this but, yes, this is it. The only thing I'll need to change is the team name in quotes if i add more teams, which i tried and it worked. The only issue is if you go to the "hitting" tab and put something in "years" and "salary" column it works across the team tabs but doesn't work for the pitchers. This is beautiful. I'm assuming this same formula works in google sheets?

[Glenn Kennedy]

Pitcher, B1. Delete the trailing space. "Salary " should be "Salary".

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[DJunqueira]

I think I corrected my main mistakes.

[Coly88]

Glenn,


here's what I see on B1, Pitchers.

Everything looks ok maybe I'm missing something


Attachment 757866

[Glenn Kennedy]

You need a VERY BIG coffee. That is from ABD, cell A9. I asked you to go to Pitcher, B1 and remove the trailing space after the word Salary in the header.

[AliGW]

Administrative Note @Coly88:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[Coly88]

That worked! Thank you for everyone's help! I really do appreciate it. Happy Holidays to everyone. Stay safe

[Glenn Kennedy]

Woo Hoo!

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[AliGW]

PLEASE UPDATE YOUR PROFILE! Don't ignore my request, please. Thanks.

Happy Christmas!

[Glenn Kennedy]

Incidentally, you could save a lot of time if you picked up the team name from a cell reference, rather than hard-coding it into the formula. That way, creating a new sheet would take only a few seconds.