IF Statements How can I shorten this formula

[willro]

I have been trying to create a spreadsheet that will look up references in in multiple tables in the worrkbook. I have come to a problem with IF statements. I have reached the maximum IF's I can use, however I need to reference three more tables, as well as it needs to differentiate between males and females. In other words with respect to males and females I need it to reference two different sets of tables.....Please help. If you need more infoplease just let me know.

This is the formula:

=IF(BD4>8.99,VLOOKUP(BE4,Sheet1!AB4:AC51,2),IF(BD4>7.99,VLOOKUP(BE4,Sheet1!Y4:Z51,2),IF(BD4>7.49,VLOOKUP(BE4,Sheet1!V4:W51,2),IF(BD4>6.99,VLOOKUP(BE4,Sheet1!S4:T51,2),IF(BD4>6.49,VLOOKUP(BE4,Sheet1!P4:Q51,2),IF(BD4>5.99,VLOOKUP(BE4,Sheet1!M4:N51,2),IF(BD4>5.49,VLOOKUP(BE4,Sheet1!J4:K51,2),0)))))))

 

[wsjackman]

I am also curious if I can protect specific cells in a workbook??

if you protect the workbook
all cells, except ones you specifically unprotect, will be protected

 

[willro]

Thank you so much!! You have been tons of help. However I have yet another question. The formula previous discussed....how can I make it apply to all cells in the column? Can I imbed that in the formula or do I have to copy & paste it to the next cell below(when I do this I must adjust the formula for the new cell position, may take hours)?

 

[wsjackman]

by making the look up ranges be absolute, you can just drad the formula down and the look up values will increment , but the look up ranges wil stay the same

rather than entering all of the $s you can highlight the range and press F4

=IF(BD4>5.99,
IF(BD4>8.99,
VLOOKUP(BE4,Sheet1!$AB$4:$AC$51,2),
IF(BD4>7.99,
VLOOKUP(BE4,Sheet1!$Y$4:$Z$51,2),
IF(BD4>7.49,
VLOOKUP(BE4,Sheet1!$V$4:$W$51,2),
IF(BD4>6.99,
VLOOKUP(BE4,Sheet1!$S$4:$T$51,2),
IF(BD4>6.49,
VLOOKUP(BE4,Sheet1!$P$4:$Q$51,2),
VLOOKUP(BE4,Sheet1!$M$4:$N$51,2)))))),
IF(BD4>5.49,VLOOKUP(BE4,Sheet1!$J$4:$K$51,2),
IF(BD4>4.99,VLOOKUP(BE4,Sheet1!$G$4:$H$51,2),
IF(BD4>4.49,VLOOKUP(BE4,Sheet1!$D$4:$E$51,2),IF(BD4>3.99,VLOOKUP(BE4,Sheet1!$A$4:$B$51,2)
))))) <!-- / message -->

 

[willro]

I now have the right formulas for boys and girls, and I used the boys & girls formula chart with the if statement you gave me. =IF(BD4="M",$Males,$Females). However the formula is for a specific row, how do I get it to change for each row.
=IF(BE4>5.99,
IF(BE4>8.99,
VLOOKUP(BF4,Sheet1!$AB$4:$AC$51,2),
IF(BE4>7.99,
VLOOKUP(BF4,Sheet1!$Y$4:$Z$51,2),
IF(BE4>7.49,
VLOOKUP(BF4,Sheet1!$V$4:$W$51,2),
IF(BE4>6.99,
VLOOKUP(BF4,Sheet1!$S$4:$T$51,2),
IF(BE4>6.49,
VLOOKUP(BF4,Sheet1!$P$4:$Q$51,2),
VLOOKUP(BF4,Sheet1!$M$4:$N$51,2)))))),
IF(BE4>5.49,VLOOKUP(BF4,Sheet1!$J$4:$K$51,2),
IF(BE4>4.99,VLOOKUP(BF4,Sheet1!$G$4:$H$51,2),
IF(BE4>4.49,VLOOKUP(BF4,Sheet1!$D$4:$E$51,2),IF(BE4>3.99,VLOOKUP(BF4,Sheet1!$A$4:$B$51,2)
)))))

This is for the BE4 and BF4 rows, it now must change to BE6 and BF6 and then BE8 and BF8 and so on. What changes do I need to make in the formula to account for this???

 

[wsjackman]

if you are moving down the sheet with the new formula location just copy the cell and paste and it should automatically show the new rows.

 

[willro]

I think that I am complete with my excel sheet. Thanks I would not have been able to do it without you!!