I have a question involving exceeding the amount of IF statements.
Cell A1 has drop down list of 100 choices, the user selects one
Cell B1 has another drop down list of 100 choices, of which the user selects
one
Cell C1, once a selection is made in both A1and B1, give an answer from 4
possible values in C1.
i.e if a1 = dog and b1 = chair then c1 = yes
The drop down list is important for the use since I do not desire to
display a 100 choices for A1 and B1.

I know that I can only use 7 nested IF statement in a cell.
What would be the best way to display the values with drop down list.
Insert criteria in cell, use a VBA macro?
If so what would the cell or VBA look like?
With a 100 possible selections in both a1 or b1, the simplest way would be
the best.

thanks to anyone who can help.
Mike
--
mike

Hundred nested functions is really hard to work with anyway.
Try VLOOKUP insttead.

"mike" wrote:

I have a question involving exceeding the amount of IF statements.
Cell A1 has drop down list of 100 choices, the user selects one
Cell B1 has another drop down list of 100 choices, of which the user selects
one
Cell C1, once a selection is made in both A1and B1, give an answer from 4
possible values in C1.
i.e if a1 = dog and b1 = chair then c1 = yes
The drop down list is important for the use since I do not desire to
display a 100 choices for A1 and B1.

I know that I can only use 7 nested IF statement in a cell.
What would be the best way to display the values with drop down list.
Insert criteria in cell, use a VBA macro?
If so what would the cell or VBA look like?
With a 100 possible selections in both a1 or b1, the simplest way would be
the best.

thanks to anyone who can help.
Mike
--
mike

Posted the response below 3 days ago to your earlier posting:
------- ------
One approach ..

Assume your 100 x 100 x 100 response/answer table is in a sheet: X,
within A1:C100 as below, where col C = answers

dog chair yes
dog table no
dog food maybe
cat chair no
cat table maybe
cat food yes
etc

Then in another sheet, where you have the corresponding DVs running in cols
A and B, from row1 down, you could place this array formula in C1, and
confirm the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing
ENTER):

=IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$100,MATCH(1,( X!A$1:A$100=A1)*(X!B$1:B$100=B1),0)))

Copy C1 down as far as required. Col C will return the answers for the DV
selections made in cols A and B.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote:
I have a question involving exceeding the amount of IF statements.
Cell A1 has drop down list of 100 choices, the user selects one
Cell B1 has another drop down list of 100 choices, of which the user selects
one
Cell C1, once a selection is made in both A1and B1, give an answer from 4
possible values in C1.
i.e if a1 = dog and b1 = chair then c1 = yes
The drop down list is important for the use since I do not desire to
display a 100 choices for A1 and B1.

I know that I can only use 7 nested IF statement in a cell.
What would be the best way to display the values with drop down list.
Insert criteria in cell, use a VBA macro?
If so what would the cell or VBA look like?
With a 100 possible selections in both a1 or b1, the simplest way would be
the best.

thanks to anyone who can help.
Mike
--
mike

Max
When I tried the formula below, received blank cell. Maybe the True part, "
", is misplaced in the formula.
Additionally, I needed the sheet to display only 3 cells, A1, B1 and C1
with A1 and B1 being a drop down selection list and C1 displaying the
correct value out of 4 possible choices, as or right now all choices in a1
and b1 are listed.
Thanks for your help.

--
mike


"Max" wrote:

Posted the response below 3 days ago to your earlier posting:
------- ------
One approach ..

Assume your 100 x 100 x 100 response/answer table is in a sheet: X,
within A1:C100 as below, where col C = answers

dog chair yes
dog table no
dog food maybe
cat chair no
cat table maybe
cat food yes
etc

Then in another sheet, where you have the corresponding DVs running in cols
A and B, from row1 down, you could place this array formula in C1, and
confirm the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing
ENTER):

=IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$100,MATCH(1,( X!A$1:A$100=A1)*(X!B$1:B$100=B1),0)))

Copy C1 down as far as required. Col C will return the answers for the DV
selections made in cols A and B.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote:
I have a question involving exceeding the amount of IF statements.
Cell A1 has drop down list of 100 choices, the user selects one
Cell B1 has another drop down list of 100 choices, of which the user selects
one
Cell C1, once a selection is made in both A1and B1, give an answer from 4
possible values in C1.
i.e if a1 = dog and b1 = chair then c1 = yes
The drop down list is important for the use since I do not desire to
display a 100 choices for A1 and B1.

I know that I can only use 7 nested IF statement in a cell.
What would be the best way to display the values with drop down list.
Insert criteria in cell, use a VBA macro?
If so what would the cell or VBA look like?
With a 100 possible selections in both a1 or b1, the simplest way would be
the best.

thanks to anyone who can help.
Mike
--
mike

Here's a working sample for the earlier response, Mike:
http://cjoint.com/?iusfBXwNsw
mike.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote:
Max
When I tried the formula below, received blank cell. Maybe the True part, "
", is misplaced in the formula.
Additionally, I needed the sheet to display only 3 cells, A1, B1 and C1
with A1 and B1 being a drop down selection list and C1 displaying the
correct value out of 4 possible choices, as or right now all choices in a1
and b1 are listed.
Thanks for your help.

--
mike

Max
It works perfectly
Thank you very much
--
mike


"Max" wrote:

Here's a working sample for the earlier response, Mike:
http://cjoint.com/?iusfBXwNsw
mike.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote:
Max
When I tried the formula below, received blank cell. Maybe the True part, "
", is misplaced in the formula.
Additionally, I needed the sheet to display only 3 cells, A1, B1 and C1
with A1 and B1 being a drop down selection list and C1 displaying the
correct value out of 4 possible choices, as or right now all choices in a1
and b1 are listed.
Thanks for your help.

--
mike

Good to hear that, Mike. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote in message
...
Max
It works perfectly
Thank you very much

Max
It works well but I had a problem when I expanded the a,b,c columns answers
to 250. Eventually, the rows may reach 2000. I tried changing the index
statement to a number higher than 100 but the statement responded with a N/A.
Is there a max amount of rows in the array?
--
mike


"Max" wrote:

Good to hear that, Mike. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote in message
...
Max
It works perfectly
Thank you very much

-- Max
I had a problem when I expanded the a,b,c columns answers
to 250. Eventually, the rows may reach 2000. I tried changing the index
statement to a number higher than 100 but the statement responded with a N/A.
Is there a max amount of rows in the array?
--

mike


"mike" wrote:

Max
It works well but I had a problem when I expanded the a,b,c columns answers
to 250. Eventually, the rows may reach 2000. I tried changing the index
statement to a number higher than 100 but the statement responded with a N/A.
Is there a max amount of rows in the array?
--
mike


"Max" wrote:

Good to hear that, Mike. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote in message
...
Max
It works perfectly
Thank you very much

.. statement responded with a N/A.

You probably forgot to array-enter the formula. Remember to press
CTRL+SHIFT+ENTER each time that you edit the top cell array formula/before
recopying down the col. Confirm it's correctly array-entered by looking at
the formula bar. You should see curly braces { } wrapped around the formula
by Excel. If you see no curlies, you gotta re-do the array-entering again
(click inside the formula bar, re-press CTRL+SHIFT+ENTER).

Btw, I posted this response to your other thread.
---------------
It should work ok, eg as per below.

=IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$4000,MATCH(1, (X!A$1:A$4000=A1)*(X!B$1:B$4000=B1),0)))

Ensure all the ranges within the INDEX/MATCH are identically sized. Look out
for possible typos. Entire col references cannot be used.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mike" wrote in message
...

-- Max
I had a problem when I expanded the a,b,c columns answers
to 250. Eventually, the rows may reach 2000. I tried changing the index
statement to a number higher than 100 but the statement responded with a
N/A.
Is there a max amount of rows in the array?
--

mike