HELP! using the IF function to verify a text value in a range of cells an return a value.

[cymarel]

Hi,

I'm making a spreadsheet that helps me plan the season for a cyclist in a game call Pro Cycling Manager.

I have a sheet with the names of my cyclist and what races the should race in a given week. I select the races from a pull down menu on the riders row for each week, and I've design a calendar on another sheet so every time I select a rider for a race it appears on the calendar.

I've done it with this formula:

=IF(PLAN!$L$10=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$11=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$12=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$13=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$14=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$15=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$16=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$17=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$18=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$19=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$20=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$21=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$22=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$23=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$24=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$25=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$26=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$27=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$28=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$29=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$30=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$31=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$32=RACE!$G5,RACE!M5,"")&IF(PLAN!$L$33=RACE!$G5,RACE!M5,"")


But I was wondering if there is a way I can make the function If work with a range o cells for the first value so I don't have to input every road manually since this is a formula I have to apply in over 100 cells and I have to put absolute reference cells because if not excel mixes them up.

Can anybody help me.

Attached are some sreenshoots of what I'm trying to achieve.

I'm working on office 2010 for mac.

Screen shot 2012-07-29 at 19.25.50.jpgScreen shot 2012-07-29 at 19.25.31.jpgScreen shot 2012-07-29 at 19.25.13.jpg

[Richard Buttrey]

Have you checked out the VLOOKUP() function which will simplify all that IF stuff.

[cymarel]

I found some info on the web about VLookup, but i'm not sure I understand how it works.

---------- Post added at 08:17 PM ---------- Previous post was at 08:14 PM ----------

And the results should be input on a different sheet than the date is analyzing.

[Richard Buttrey]

How about uploading your data and an example showing what results you expect to see.

[cymarel]

In the file I uploaded the first three sheets are named: PLAN, RACE and CALENDAR.

PLAN shows a list of names, groups and role each cyclist in my team has and what races I have to select for him to race in the upcoming weeks of the season. Each race can be selected in columns labeled as the weeks of each month of the season. And I've put a pulldown menu with the available races on each cell that colides with the riders row and the week column.

RACE shows the races that can be race during the season, the date its starts, the date it finishes, the name, the number of stages, allowed minimum riders, and allowed maximum riders, with a cell for each stage that contains a text and color symbol of what type of stages it is.

CALENDAR is a Month by Month calendar of a given year, with three rows under each week.

I wan't the calendar to update automatically each time I select a race for a rider in the PLAN sheet so I can see what races are being race by my team at each given week. For this I have left the three rows under each week of the calendar (Because a team can't race more than three races in a week).

I did what I want it to achieve and you can see it in the third week of January in the calendar, with two races, as an example. But I found with the formula I'm using (the IF function) is going to be a time consuming task if I have to input 164 races and is subsequente stages. If only I could select a range of Cells in a column for the IF function I would have to input each one manually in the formula.

I hope I made my self clear as spanish is my native language.

Thanks in advance for you interest and help.