Dynamically Populated Drop-Down List Boxes

All:
I am attempting to create a worksheet (A) that populates itself with specific baseball player statistics (currently more than 108,000) all of which are located on another worksheet (B)-which is in the same workbook) based on the inputs of 2 drop-down list boxes (hereafter referred to as ‘DDLB”), located on Row 1 in worksheet A.

I have created a hard-coded DDLB #1 using a list created on Worksheet C. It selects the year in which you want to select a distinct team name (which will be DDLB #2).
The 2 Drop-down list boxes are supposed to “daisy-chain” to each other and be dynamically created. DDLB #2 is populated by what’s selected in DDLB #1.

Because Worksheet A Row 1 has these 2 DDLB’s, I have designated Row 2 to be the header row which displays the stat headings of each column, such as “Player Name”, “AB”, “Hit”, etc.
Row 3 thru Row 50 will contain each individual players stats when selected via, you guessed it, a DDLB #3 on each rows “Players Name” Column. Once selected, the rest of the row will be automatically populated with the rest of the players stats, dependent on the year and team selected from DDLB #1, and DDLB #2.

My questions are:
1. Is there an easy way to create a dynamic list for DDLB #1, showing distinct years, sorted numerically backwards) based on the thousands of years listed in worksheet B? I would like to do this, so that when I add next years data to worksheet B, I won’t have to change anything else in the workbook… It’s all “dynamic”
2. How do I create these dynamic DDLB’s?
3. How do I populate each Row with the other stats… VLOOKUP?

Please feel free to request additional information. Thanks in advance to anyone wanting to help me with this challenge.
"Small-experience with Excel"

see big yellow banner - how to upload your workbook.
Far easier to give a definitive solution with the structure in-front of us to check.
With a large sheet you need the most efficient search not a cumbersome 'snail's pace'

Take a look here

https://www.contextures.com/xldataval02.html

And see yellow banner on how to post a sample workbook.

OK... Hopefully this helps, but only yearid 2020 has data

SORRY the file is still too big. You need a lot of data to sift thru for this thing to work.

Interested parties can leave their e-mail addresses and I will send the file there... SORRY

sorry i do not give out my email address as most of others do not.
if you want help merely zip the file and upload it.

Perhaps this will be a starting place:
1. Insert a row at the top of the Players sheet to put in the column headers.
2. Populate a list of teams in column C on the DDLB sheet using: =IFERROR(INDEX(Players!J$2:J$791,MATCH(0,INDEX(COUNTIF(C$1:C1,Players!J$2:J$791),,),)),"")
3. The data validation for cell H1 on the Main sheet has a source of: ='DDLB Text'!$C$2:$C$31
4. Cell on the Main sheet is populated using: =INDEX(Players!L$2:L$791,MATCH(Main!H$1,Players!J$2:J$791,0))
5. On the DDLB Text sheet column E displays the full roster using:

Formula:

Please Login or Register  to view this content.

6. Column F identifies the players that have not been chosen using: =IF(E2="","",ISERROR(MATCH(E2,Main!D$4:D$55,0)))
7. Column G lists the players that have not been chosen using: =IFERROR(INDEX(E$2:E$40,AGGREGATE(15,6,(ROW(E$2:E$40)-ROW(E$1))/(F$2:F$40=TRUE),ROWS(G$2:G2))),"")
8. The source for the cells in column on the Main sheet is populated using: =OFFSET('DDLB Text'!$G$2,0,0,SUMPRODUCT(--('DDLB Text'!$G$2:$G$40<>"")),1)
I feel that we will need to see the column headers on the Players sheet to know which columns represent which stats.
Note that the site accepts the file when uploaded as an xlsb
Let us know if you have any questions.

MANY THANKS!!!! LOOKS GREAT!!!

A full version of the data has been uploaded to my github account (file extension .xlsb)

The Players! worksheet Row 1 identifies all columns that need to be populated on the Main! worksheet (they match with same name)

on my git-hub account .xlsb file is the full file. Even zip'd, it is over 13MB

BTW... I'd like to use full team names, sorted, if possible. Also, player lastname, firstname should appear first & alphabetically listed.

I don't have a git hub account and the file is too large to upload here so I'll have to just describe the changes I made.
1. You already made the adjustment for the team names in column C on the DDLB Text sheet. Attempting to use an array formula to put the teams in alphabetical order made Excel unresponsive for about 15 minutes so I am leaving that alone and not going to attempt to put players in alphabetical order either.
2. For the players column on the DDLB Text sheet:

Formula:

Please Login or Register  to view this content.

Note that I copied this down to cell E45
3. The formula for column F is not changed but also dragged down to row 45
4. The formula for the For DD column:

Formula:

Please Login or Register  to view this content.

Note that it should be dragged down to cell G45.
5. The formula for E4:Q12 on the Main sheet:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thanks for the info... I'll play with it for awhile.

I don''t think you need an account to get a file from git-hub... Just copy/paste this link into your browser, and you should see 2 files there... then just hover your cursor over the filenames... one is the full .xlsb file

github.com/oradba4u/Players-Pitchers

I downloaded the file, however without an account, I don't think that I can put the updated file with my formulas back on github so that you can see how they work.
Hopefully the description is enough to go by.
Let us know if you have any questions.

See if the following helps:
1. On the DDLB Text sheet select cell C2 and drag the fill handle down to cell C140 (it took my computer several minutes to update)
2. Change the data validation source for cell H1 on the Main sheet from ='DDLB Text'!$C$2:$C$31 to ='DDLB Text'!$C$2:$C$140
Let us know if you have any questions.

I did that, but there is no change to Main! H1 (team name) when I select a new year on Main! D1. In other words, Main! H1 team names Drop-Down List remains what they were no matter what year I select.

Also I noticed that the lists that are supposed to be built on worksheet DDLB Text! (Columns E & G) are blank (they don't appear to be updating) but there are formulas in all 140 rows

My SQL database reveals there are 139 unique team names, so I think DDLB Text! Column C should have more than 50 entries

@oradba4u, I have sent you the file with the changes mentioned in post #13 applied.
Let us know if you have any questions.

@Lupovico, you are welcome to participate in coming up with a solution to oradba4u's query if you would like.

I think you're close... However, when I select 1963, I get a lot of teams (like the San Diego Padres & Troy Haymakers) that didn't play in 1963

Inserted two columns (D:E) on the DDLB Text sheet.
Column D identifies which teams played in a certain year using: =COUNTIFS(Players!E$2:E$108790,C2,Players!H$2:H$108790,Main!D$1)>0
Column E produces the list of teams that played using: =IFERROR(INDEX(C$2:C$140,AGGREGATE(15,6,(ROW(C$2:C$140)-ROW(C$1))/(D$2:D$140=TRUE),ROWS(E$2:E2))),"")
The source for the data validation drop down in cell H1 (Main) is: =OFFSET('DDLB Text'!$E$2,0,0,SUMPRODUCT(--('DDLB Text'!$E$2:$E$40<>"")),1)
Note that I am assuming there were not more than 39 teams in any year. If that is not correct let me know and I'll make an adjustment.
Note that changing the year and team will not clear the players that are listed in D4 and down. To do that automatically would require VBA (I think it is called a change event) of which I don't know enough to help.
I feel that it would be a good idea to include a github link to the most recent version of the file with your future posts so that someone whom may be interested can see what has been done.
Let us know if you have any questions.

Man, this is starting to shape up VERY nice

A couple of things,,,
1) Is there a small tweek that allows the player names to appear in the DDLB LastName first, FirstName last? It's already sorted alphabetically by LastName which is GREAT!
2) I checked and the Maximum number of teams in a season is the current 30, so you're good to go with a possible 39 teams in DDLB Text Column E.
3) I also checked and the Maximum number of players who (at one time or another) played on a teams roster is 67 (2019 Seattle Mariners), so DDLB Text! G and I columns need to be lengthened to $G$76 and $I$76 respectively... this allows for potentially 8 players more (for a total of 75 players) than the current "record" of 67 players.

I'm working on a "trimmed" player list, eliminating columns that don't need to be on the Players! worksheet. However, this should NOT affect you and your work.
I'll be adding a "Pitchers" data worksheet to be able to select the pitchers section on the Main! worksheet.

Also, I've developed a NEW worksheet so we can add players/pitchers without having to skip rows. Please see the repository for the new spreadsheet. It is simply named "Players & Pitchers.xlsb". Please use this new file for all edits/mods, etc. THANKS!

One last item... Can you place a "reset" button on the Main! worksheet (Like at $A$1) that clears all the worksheet Main! data, but not the formulas, etc.?

THANKS FOR ALL THE HARD WORK YOU'RE DOING AND HAVE DONE!

As for #1 the formula in column G on the DDLB Text sheet is modified to read:

Formula:

Please Login or Register  to view this content.

Also the formula in E4:Q12 on the Main sheet is changed to read:

Formula:

Please Login or Register  to view this content.

As for #3 select cells G45:I45 (as there is a formula in H45) and drag the fill handle down to cell I76.
As for bench players, we may need to add a helper column on the main sheet.
As for pitchers, it may be easier to have them on a separate sheet (although this goes against my usual recommendation to put all of the data on one sheet).
That said, it would be easier to help with the pitcher and bench player items if we could see where the file is now, please update the workbook on the github site and put the link in your next post.
Let us know if you have any questions.

I have changed the spreadsheet and migrated everything to the new format.
I lengthened the DDLB Text! columns G, H & I to allow for 75 players.
I lengthened the DDLB Text! column E to allow for 40 teams.

All players are now on Main! rows 1-29
Pitchers are now listed on Main! rows 31-55
Extra players (which are not used by the game, but can be switched manually) are listed on rows 59-68
Extra pitchers (which are not used by the game, but can be switched manually) are listed on rows 70-89

Everything seems to work so far.

The spreadsheet has been uploaded to the repository... it is named "Players & Pitchers.xlsb"

Populating the position players on Main! (rows 2-29 and 59-68) seems to work just fine.

To Do:
"Trim" Players! worksheet to only usable data.
Add "Pitchers" worksheet to populate pitchers rows on Main!

Add "RESET" Button to Main! ($A$1)? PLUS I think a "reset" should be automatically sent to clear the Main! worksheet when a new year or team is selected. What are your thoughts on this?

Do you still want to populate cells D14:D29 on the Main sheet based on batting average.
I found that some of the values in column P on the Players sheet are text (if you unselect right alignment on that column then those that are text will align to the left).
It is a rather easy fix. Select cells P2:P108790 > select Text to Columns > select Finish (now all values will align to the right).
Let us know if you have any questions.

I deleted some columns on the Players! sheet to lighten the size of the spreadsheet, and I messed up populating Main! sheet E4:Q29 and E59:Q68 (the players portion of Main!) in the process. MY BAD!
However, I think I got everything on DDLB Text! to work normally. Notice that I put a "|____|" at the end of the formulas ranges in columns C, D, E, G, H, & I.

The latest and greatest can be found in the repository.

After this "snafu" is resolved, I will add a "Pitchers" worksheet so we can populate Main! E31:X55 and E70:X89

The formula should now read:

Formula:

Please Login or Register  to view this content.

Note that two of the column headings (Hit and ERR) on the Main sheet do not match column headings on the Players sheet.
Please put a link to the most recent version of the file in each post.
Let us know if you have any questions.

Thank You for including the link in the post.
Please replace the formula in cell K1 with:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Everything seems to be working fine. I have uploaded the latest spreadsheet into the Git-Hub repository here:

https://github.com/oradba4u/Players-Pitchers

On Main! I re-formatted columns E-Z to make the columns look nice & uniform.

On Main! I merged cells G1, H1 & I1 to accommodate a long team name.

On Main! I added a drop-down list box in cell P1. Its list is on DDLB Text! Column F. The reason for this DDLB is to change the values on Main! Cells N4:N12, N13:N25 and N59:N68. Here’s what I need:

When “ERR” is selected in the DDLB labeled “DEF/ERR” (P1) the selected players statistic is updated with the value “ERR” on Players! Column Z. When “DEF” is selected in the DDLB labeled “DEF/ERR” (P1) the selected players statistic is updated with the value “FLD” on Players! Column Y. These values represent the number of ERRors the player had, OR his FieLDing percentage.

Please notice that the header lines on Main! (3, 13, & 58) are already programmed to change, depending on what has been selected in Main! P1.

Cosmetic change: Is it possible to have the team names arranged alphabetically (by city) on the Main! DDLB?





As always, many thanks!

I suggest replacing DEF with FLD so that things will be uniform based on the Players worksheet.
Also suggest conditional formatting in column N i.e. =N4>=1 [General] =N4<1 [#.000]
As to putting teams in alphabetical order by city a new column (F) is added to the DDLB Text sheet.
The new column is populated using the following array entered formula**: =IFERROR(INDEX(E$2:E$41,MATCH(SUM(COUNTIF(F$1:F1,E$2:E$41)),COUNTIF(E$2:E$41,"<"&E$2:E$41),0)),"")
**Note that array entered formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

See if the following conditional formatting rules will give the correct the output for column N.
for ERR is: =P$1="ERR" [General]
for DEF it is: =P$1="DEF" [#.000]
Let us know if you have any questions.

I’ve been testing the spreadsheet Main!, and it seems to be working flawlessly. I added a “RESET” button that clears all yellow drop-down list boxes of their contents, not their formulas. The result is all other cells are cleared, once the names are deleted.

Now, a word about the lavender colored cells, and the Fielding! Sheet. These cells (R4:U12, R14:U29, and R59:U68) represent up to 4 positions the named player in D column played during the year selected in cell C1. The Fielding! Sheet contains all fielding data by playerID and yearID. It also lists his POSition, and Games played at that position. For example, Fielding! rows 13-15 show that Hank Aaron played 3 different positions during the 1955 season… 104 games in RightField, 30 games in LeftField, and 27 games at SecondBase. So, if Hank Aaron was chosen from the Drop-down list box for the year 1955 Milwaukee Braves his stats would be populated in columns E:Q , PLUS this would be populated in columns R:U. The columns should be populated by the most games played at a position listed first, the second most games at a position listed second, and so on. ONLY USE the top 4 positions! (Hint: The Fielding! Sheet is pre-sorted with each players most games listed first, second most second, etc. for any given year)

qqq.png


Positions are as follows:
1-Pitcher
2-Catcher
3-FirstBase
4-SecondBase
5-Thirdbase
6-Shortstop
7-LeftField
8-CenterField
9-RightField
D-Designated Hitter (set manually by user)

As always, the latest spreadsheet is located here:

https://github.com/oradba4u/Players-Pitchers

This adds five columns (AA:AE) on the Main sheet all of which may be moved and/or hidden for aesthetic purposes.
Column AA is populated using:

Formula:

Please Login or Register  to view this content.

Columns AB:AE are populated using:

Formula:

Please Login or Register  to view this content.

Columns R:U are populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

the 1st entry seems to be working fine. After that values are repeated. The copy/pastes you suggested don't seem to function.
BTW... all the lavender cells after the first row were blank (no formulas) I think I made things worse by attempting to correct absolute and mixed cell references

BTW... Using columns over to the right on Main! are OK. We can hide them later if needed.

Correction to formula in AB4:

Formula:

Please Login or Register  to view this content.

Note drag the fill handle over to cell AE4 and then down to cell AE29 then delete the formula from AB13:AE13
Correction to formula in R4:

Formula:

Please Login or Register  to view this content.

Note drag the fill handle over to cell U4, then down to cell U12.
Copy R4:U12 (Ctrl + c) and select cell R14.
Paste (Ctrl + v) should fill cells R14:U22.
Drag the fill handle down to cell U29.
Let us know if you have any questions.

I think the spreadsheet is coming along nicely, and I think there are only 2 more features to be added. Here’s the first one:

HBP (Hit By Pitch) – On Main!, (Players Sections only, NOT Pitchers) there is a column (Col K) provided for a 1 letter designation (A-I) to indicate this players susceptibility to being hit by a pitch! “A” being VERY susceptible, and “I” being very little. Here’s how it should work:

Step 1 – Get the year of the player from Main! C1
Step 2 – Find his matching record on Players! And read his HBP
Step 3 – Calculate his Adjusted BHP by this method: AdjHBP=(HBP/AB)*502 (to nearest 1 decimal place)
Step 4 – On the HBP! Sheet find what letter designation to put in Main!(col K) for this batter.
This is done by finding the number in the proper year row on HBP! That is closest (+ or -) to the number calculated in step 3.

Example: Mark McGwire had 11 HBP in 317 AB (AtBats) in the 1995 season.
Step 1. 1995
Step 2 AdjHBP=(11/317)*502=17.4
Step 3. On the HBP! row for 1995 we see that the column titled “B” contains the number closest to 17.4 (which is 17.7)
Step 4. Place the letter “B” on Main! (col K) for that player

NOTE: Any player with 0 (zero) HBP will automatically become “I”

As always, the spreadsheet can be found here:
https://github.com/oradba4u/Players-Pitchers

On the Players Sheet:
Column AF is populated using: =IF(AE2=0,"",ROUND(AE2/K2*502,1))
Columns AG:AO are populated using: =IF($AE2=0,"",ABS(INDEX(HBP!B$2:B$152,MATCH($G2,HBP!$A$2:$A$152,0))-$AF2))
Column AP is populated using: =IF(AE2=0,"I",INDEX(AG$1:AO$1,MATCH(MIN(AG2:AO2),AG2:AO2,0)))
Note that AG1:AO1 are populated with the letters A:I
On the Main sheet column K is populated using: =IF(D4="","",INDEX(Players!AP$2:AP$108790,AGGREGATE(15,6,(ROW(Players!AP$2:AP$108790)-ROW(Players!AP$1))/(Players!A$2:A$108790=AB4)/(Players!G$2:G$108790=C$1),1)))
Let us know if you have any questions.

EDIT: In attempting to send you the current copy of the file I get the message "Remote server returned an error -> 552 message size exceeds maximum size tnmpmscs'"
File size is now 30 Mb as the above adds approximately 1.2 million formulas to the file.
Perhaps you could attempt to populate the columns as described above using the copy of the file that was previously posted at github without having to see my copy of the file.
As to further assistance it will probably have to be on a smaller sample of the file, i.e. only 2000:2020, as I believe I remember you saying that github would allow you only 25 Mb

Combined the formulas on the Players sheet into one array entered formula** in column AF:

Formula:

Please Login or Register  to view this content.

Modified formula for column K on Main sheet:

Formula:

Please Login or Register  to view this content.

File size is down to 18.3 Mb and appears to have sent.
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

It is working great! Many thanks!

I have re-formulated the Players! DEF column to make this a whole number (Multiplied the 3 decimal number x 1000) in hopes of making Main! column $O (DEF) reflect a 3 digit whole number... no go.

Could you send me the correct formula to plug into Main! $O to make it behave? Thanks. I'm not too bad at Excel, certainly no match for your talents, but I get lost when I start dealing with any formulas longer than 40 characters! Your formulas are great! I follow them mostly, but wouldn't want to attempt to modify them!

I will continue my formatting the final worksheet (Pitchers) for this project... I'm hoping it will be pretty easy and straight forward

On Players sheet column Y: =INT(IF($Z2=0,".999",($AB2+$AC2)/($AA2))*1000)
Select cell X2 > select Format Painter > select cell Y2 > double click the fill handle to send down.
On the Main sheet > selected cell M4 > select Format Painter > select cell O4
Let us know if you have any questions.

While testing the Main! worksheet, I discovered a glitch in selecting a player with a space in their last name like "La Stella" or "Van Halen"
When I select Player names from the Drop-Down List Box, columns E:R fill-in with "#NUM" errors. If I CONCAT the last name in the Players! sheet to, say, "LaStella", it seems to work fine.
The same is NOT true if a FirstName had a space in it - It seems to work fine as is.

Examples to try:
1884 Pittsburgh Alleghenys - Live Oak Taylor (a space in first name is OK)
1884 Providence Grays - Old Hoss Radbourn (a space in first name is OK)
2016 Chicago Cubs - Tommy La Stella (a space in last name = #NUM error)

Please send the fix here, as my newest updated spreadsheet has many modifications that I'm still working on, etc.. since my last git-hub posting.

P.S. I could change all the Last Names to a one word name like "LaStella" on the Players! worksheet... if that would be best, let me know.

On the Main sheet put the following into cell E4, copy across and then down:

Formula:

Please Login or Register  to view this content.

Paste the following into cell AB4 and copy down:

Formula:

Please Login or Register  to view this content.

On the Players sheet change the text in cell AE1 to anything other than HBP (i.e. #HBP) and then type HBP in cell AF1.
Let us know if you have any questions.

Now that you fixed the LastName with a space problem, it now makes the FirstName blow-up with the same kind of "#NUM" error!
Good thing we don't use a MiddleName!!!!!!

Hopefully I have it this time (tested with the three names given in post #43).
For cells E4:R12 and then E14:R29

Formula:

Please Login or Register  to view this content.

For cells AB4 and down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I've un-earthed a glitch in the GAM-P cells
set-up: 2017 Houston Astros J.D. Davis
He is supossed to have (it is correct in the Fielding table) in the GAM-P cells
22-5 2-1 2-3 NULL
But he has instead:
22-5 2-1 2-1 NULL
the info is also snafu'd in the AC-AF columns on Main! (which I suspect, is where the problem is)

ALSO: If possible, can you NOT show any pitchers in the Drop-Down List Boxes for Players? (Don't show IF POS=1)
It shortens the list, and pitchers usually don't bat. If they do bat, their hitting stats
will be in the pitchers data, which, hopefully will be the last hurdle for you to deal
with in this project.

As always, many THANKS!!!!

The latest file is at:
https://github.com/oradba4u/Players-Pitchers

You are correct, the issue is with the formula in AC:AF.
Change the formula in AC4 to read:

Formula:

Please Login or Register  to view this content.

Once changed copy the formula over and down to cell AF29.
Take a look at Aoki Nori (2017 Houston Astros) who apparently pitched once in 2017 vs. 113 times that he played in the field, so I would assume he was a position player that perhaps pitched in a game that had gotten out of hand and the Astros wanted to save their bull pen.
Note that he is also listed on three separate rows as playing in right field (3664:3666), 2 rows in center and two in left during that year.
My feeling is that it will be better to wait until there is a pitchers sheet and see how to proceed from there than to try to match up pitchers using the POS column on the fielding sheet to the players sheet, only to find later that there is a better way.
Let us know if you have any questions.

You are absolutely correct about Aoki pitching 1 game, as a ruse probably because the Astros were getting creamed. Do you have a macro or VBA code that will remove exact duplicates from the Fielding! sheet? I think that might eliminate the "Aoki syndrone" you spoke about, and make the file a bit smaller , which is, in this case, much needed, considering a large sheet (Pitchers) is yet to be included.

There is a glitch (#NUM) on the Main! sheet when you select 2003 St. Louis Cardinals Woody Williams as a player from the DDLB.
I noticed the wrong PlayerID coming up in AB column (AB4 in my case) It displayed "williwo01" (which is not him)
I hard-coded his correct PlayerID "williwo02" into AB4 and voila! all the #NUM errors changed to correct data in Columns E-R

BTW... I added the Pitchers! data and trimmed the fat from some columns that aren't needed in the workbook, and removed duplicate rows in worksheets.
Everything (except the problem mentioned earlier in this message) seems to be working GREAT!

The latest & greatest version of this spreadsheet can be found at the usual location. With (hopefully) the last addition to the worksheets (Pitchers!), and the fat-trimming, the file is less than 23 MB.

This is really shaping up, thanks to your diligent & professional work. THANKS! THANKS! THANKS!

Post #49: ...Do you have a macro or VBA code that will remove exact duplicates from the Fielding! sheet?...

1. I am not sure "duplicates" is actually how we should consider similar data. For example there are three rows that show Aoki Nori playing in right field, but all have different numbers of games. So it seems that the data should show that he played right field 47 times during 2017.
2. I am not a VBA person so that would need to be done by someone else, however it may be that once a decision is made as to item #1 then a formula or Power Query approach might work.
I feel that I corrected the Woody Williams error by changing the formula in AB4 and down (Main sheet) to read:

Formula:

Please Login or Register  to view this content.

Will work on removing pitchers from drop downs in column D on the Main sheet later.
Let us know if you have any questions.

The patch worked like a champ... thanks

If the playerID, yearID, pos, AND g are all the same on the Fielding! worksheet in more than 1 row, for example, then it's a duplicate. I've already trimmed those down, and the entire spreadsheet works fine.

I agree on skipping the removal of Pitchers from the Players drop downs for now. However, keep in mind that listing pitchers on the Players drop-down list boxes has nothing to do with Player selections, but for cosmetic reasons and shortening the Drop Downs, I think it's a good idea... But for now, lets move on.

I've added the Pitchers! data worksheet in the latest posted spreadsheet, so let's start populating Pitcher columns E31-S55 in the same manner you used for players, by only using the the drop downs (Main! column D) to select pitchers from the Pitchers! worksheet. I think you'll find my titles on Main! (row 30), match the data columns on Pitchers! in the Pitcher Section. In the Pitchers Section of Main!. Columns T-X are the pitchers hitting (when they bat) stats, and can be found when you match up the playerID, teamID, and yearID on the Players! worksheet. They are labeled AB, H, HR, BB, and SO on the Players sheet. but on the pitchers stats, I added an extra "B" in front of their labels to denote that these are pitcher Batting stats (not his pitching stats), so they appear as BAB, BH, BHR, BBB, & BSO on the Main! sheet.

Found an issue by accident. It appears that the St. Louis Cardinals are not listed on the pitchers sheet.
Changes made:
On the Main sheet:
Source of drop downs for D31:D55 is: =OFFSET('DDLB Text'!$N$2,0,0,SUMPRODUCT(--('DDLB Text'!$N$2:$N$44<>"")),1)
E31:S55 are populated using:

Formula:

Please Login or Register  to view this content.

AB31:AB55 are populated using the same formula as AB4 (see post #47) as I assume the player ID will be the same.
On the DDLB Text sheet:
Column I now ignores pitchers and is populated using: =IF(OR(COUNTIFS(L$2:L$44,H2),H2=""),"",ISERROR(MATCH(H2,Main!D$4:D$29,0)))
Column L is populated using:

Formula:

Please Login or Register  to view this content.

Column M is populated using: =IF(L2="","",ISERROR(MATCH(L2,Main!D$31:D$55,0)))
Column N is populated using: =IFERROR(INDEX(L$2:L$76,AGGREGATE(15,6,(ROW(L$2:L$76)-ROW(L$1))/(M$2:M$76=TRUE),ROWS(N$2:N2))),"")
Let us know if you have any questions.

OK... Looks great!

I updated the Pitchers! sheet to show more modern team names! All still seems to be working fine.

Looks like one of the final things left to do is to fill in the Pitchers section columns $T31-$X55 with the selected pitchers hitting stats (if they have any). If they have stats, they are located on the Players! sheet.
If no stats exist there (probably AL pitchers who don't bat) or any Pitcher has 0 AtBats for that year, then leave all those columns BLANK.

On the Players! worksheet, these stats are labeled AB, H, HR, BB, and SO, but on the Main! sheet, they appear as BAB, BH, BHR, BBB, & BSO for Pitchers.. Hope this helps.

The newest file has been placed in the repository. Please use it instead of anything else you've got. Thanks

Using some of what I have learned from your examples, I was able to populate the pitchers batting stats (Columns T-X) with this formula (as their hitting stats are on the Players! worksheet):

=IF($D31="","",INDEX(Players!$E$2:$AF$108719,AGGREGATE(15,6,(ROW(Players!$E$2:$AF$108719)-ROW(Players!$E$1))/(Players!$G$2:$G$108719=$C$1)/(Players!$D$2:$D$108719=$G/(Players!$A$2:$A$108719=$AB31),1),MATCH(T$30,Players!$E$1:$AF$1,0)))

NOTE: I had to rename Pitchers columns T-X (on Main!) to the same as the players headings... AB, Hit, HR, BB, SO

ONE PROBLEM: I don't know how to insert all blanks cells (Main!) in Pitchers columns T-X if AtBats (AB)=0 - This might be the last thing I ask from you!!!

Other than that, the whole spreadsheet seems to work fine!

I'll be testing it a bit better now! THIS IS GREAT! You're amazing Mr. Mac!

And all done in less than 21.8 MB

I'll check the formatting, test, then hide all worksheets (Except Instructions! and Main! and ZIP it, and then post it. I'll give the link when I post it.

Appreciate the kind words.
I will be away from my computer for a few days, but will get back to work on this thread when I return unless someone has finished it beforehand.
Please give us the link to the most recent version of the file as I could not find a downloadable file using the link in post #47.
I hope that you have a blessed day.

The most recent "Players & Pitchers.xlsb" file has been uploaded on github. https://github.com/oradba4u/Players-Pitchers

I modified the Fielding! sheet to now include teamid. Before I was getting duplicate positions in Main! $S4-$V4 because columns $AC4-$AF4 couldn't locate the exclusive team records selected, i.e. if a player played for 2 teams during the season. (Ex. Wally Post 1960 Cincinnati Reds) has 2 Gam-P for rightfield (9), because he played rightfield for 2 different teams in 1960.

This new change to Fielding! should eliminate this problem, but the formulas in $S4-$V4 and $AC4-$AF4 on Main! need to be re-worked.

As to post #57, changes made to the Main sheet:
1. Cell AB1 is populated using:

Formula:

Please Login or Register  to view this content.

2. Cells AC4:AF4 and down are populated using:

Formula:

Please Login or Register  to view this content.

3. Cells S4:V4 and down are populated using:

Formula:

Please Login or Register  to view this content.

I'll come back to the batting stats for the pitchers once we are sure that the above formulas are correct.
Let us know if you have any questions.