Help with lengthy nested IF statement

[byrne31]

This seems like a helpful place to get some advice on how to enter in a lengthy IF statement I am having trouble with. I am running Excel 2007 and have a spreadsheet I am using for a fantasy auto racing pool.

25 people are playing and they are to choose 5 drivers and I will enter them into this table. I am entering the drivers by car number so the numbers will vary from 1 to 99. Their finishing positions vary from 1 to 43 and will be added up with the lowest score winning for that week.

I need a formula that will automatically enter the data into the table based on the finishing positions. I have a column numbered 1 to 99 representing the car numbers and all I would need to do is enter the finishing position next to the respective car number and the table will populate automatically for everyone who had that number.

The IF statement I tried looks like this: I would go up to 99 but can't...

=IF(B3=1,Q1,IF(B3=2,Q2,IF(B3=3,Q3,IF(B3=4,Q4,IF(B3=5,Q5,IF(B3=6,Q6,IF(B3=7,Q7,IF(B3=8,Q8,IF(B3=9,Q9

When I get to 9 it reaches it's max number of nested IFs and I'm not sure which other functions I could use to make this work... Any help would be greatly appreciated. I will continue to research while this question is being pondered. Thanks.

Matt

 

[par60056]

welcome to the forum. Hope we can answer all your questions.

you can use the Indirect function in this case.

=INDIRECT("Q"&B3)

 

[bhmcpfs]

Nowhere near as neat/clean for your case, but you can use defined names to get around the nesting limitation.

 

[byrne31]

welcome to the forum. Hope we can answer all your questions.

you can use the Indirect function in this case.

=INDIRECT("Q"&B3)

Thanks for this. When I entered the formula and gave it a try it returned "column 2". Then I realized I had headers on my table and I just deleted the headers and shifted the data up. Problem solved. It now Works! I appreciate it very much. This will save some data entry time for sure.

 

[par60056]

Thanks for this. When I entered the formula and gave it a try it returned "column 2". Then I realized I had headers on my table and I just deleted the headers and shifted the data up. Problem solved. It now Works! I appreciate it very much. This will save some data entry time for sure.

So with the headers, you need B3=1 return Q2, b3=3 return Q3 …

then =indirect("Q"&(b3+1))

The formula inside the call to indirect just builds a string that is the cell to reference.

 

[byrne31]

So with the headers, you need B3=1 return Q2, b3=3 return Q3 …

then =indirect("Q"&(b3+1))

The formula inside the call to indirect just builds a string that is the cell to reference.

Thanks again!

 

[par60056]

Glad to help. I rarely use indirect but it does have uses.

Good luck with your project and again welcome to the forum.