ELSE IF function? *looking to avoid nested IF*

Is there an ELSE IF kind of function in excel like in MATLAB?

I am trying to make dynamic data validation lists based on adjacent cell value.


---------------
I can do this but would like to do it without nesting anything.

EX:
IF(A=B,O1:O10,IF(A=C,P1:P10,etc...))
---------------

---------------
Looking for something like this where nothing is nested and an amendment can be tacked on to the end of the formula without worrying about squeezing it in the right place in a super long nested mess. Hopefully looking to accomplish this without anything too advanced like visual basic.

EX:
if A=B, then O1:O10
else if A=C, then P1:P10
else if ...
etc...
---------------

Thanks

Hi

You could probably use the offset command from the fixed range N1:N10, offset by a column amount based on B, C.....

But I'd need more detail on exactly what B, C... really happen to be to be able to work out an offset process.

rylo

Originally Posted by rylo

Hi

You could probably use the offset command from the fixed range N1:N10, offset by a column amount based on B, C.....

But I'd need more detail on exactly what B, C... really happen to be to be able to work out an offset process.

rylo

(ex: B, C, etc...) are cells adjacent(to the left) of the cells I am trying to create dynamic drop down menus in through data validation. They themselves are menus with various group names in text (ex: fruit, vegetable, etc...)

When (ex: fruit) is selected in the group cell the adjacent(to the right) cell needs to offer an appropriate selection (ex: list of fruits) which are referenced from another data-sheet.

The data-sheet contains columns whose data corresponds to the above mentioned group names as well as other data. The columns whose data needs to appear in the list (depending on the adjacent group name) start with C and appear every 4th column: C,H,M,R, etc.

What I have now with nesting is:
SOURCE: =IF(A14='NUTR. (data)'!$C$1,'NUTR. (data)'!$C$3:$C$100,IF(A14='NUTR. (data)'!$H$1,'NUTR. (data)'!$H$3:$H$100,etc...))

*14th row in main sheet is arbitrary, that's just the row I started with, every row in the main sheet will need to behave like it.

*C1, H1, etc... are header cells for the columns we want, they hold group names.

Hi

Example file time with details on exactly what has to be done where in relation to that file.

rylo