|I’m looking for some direction. I’m a former educator that taught basic computer applications to high school students. It was a one semester course that had to cover the basics, and I mean basics, of WORD, EXCEL, ACCESS and POWERPOINT, I was also a wrestling coach. I created a spreadsheet to manage, bouts and scoring for a pool style wrestling tournament. It worked well, a bit cumbersome for an experienced excel user, which I am not, I’m sure, but it worked for me.|
The basic function I used was the IF..THEN statement and with 6 teams there no issues. But I recently was asked to adapt it to 12 teams and my nested IF’s are not getting it done do to the 7 limitation.
I’ve researched and gooled the net and tried about all the functions that I feel will work, but each seems to come up short, or rather, my abilities to use excel are not developed enough to get it done.
Here’s what I want to do.
I use columns A – M for various data. Column B contains team names and column M contains team scoring (points earned). So Columns B/M are needed to be “tied/related” to each other for team scoring. The teams’ names are in random order in rows 5-16. And are never in an ascending or descending order.
In separate section of the spreadsheet I’m trying to alphabetize the teams and their corresponding scores. So let’s say in column P (P1-12), I list the teams in alpha order and I want their corresponding score to appear in column M (M1-12). I started column M (M1) with IF(P1=B5,M5,IF(P1=B6,M6,IF(P1=B7, M7,IF(…)))) for my scoring. Then I copied that formula to M(2-12) changing the P1 to P(2-12) accordingly. That works for the 1-6 or 7. But not so good for 8-12.
I had successes using CONCATENATE in another section, but CONCATENATE changes the score from a value to text (?). I need the score to remain a value for other computations and CONCATENATE won’t let me do it.
I tried creating a separate table and using VLOOKUP and thought I had solved the problem. Until I realized the VLOOKUP requires tables to be in ascending or descending order and the teams in column B are always in random order.
I’m unable to figure out Boolean multiplication or how to create custom VBA function.
My most recent effort was to use user defined names. I divided my schools into groups of 6 and created 2 sets of IF’s , one for each group. And named each. That was 12 x 2, or 24 defined names and then used something like: =IF(OneToSix,OneToSix,SevenToTwelve). I had limited success with it. It worked for the first 1-6, but returns #VALUE! for the 7-12.
Any direction would be greatly appreciated. Thanks