HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN CELL
 

example:
IF(OR(F3="BD",F3="CD"),B6,C6)

if cell "F3" is a certain value my formula requires different cell values,
this may occur numerous times in a formula, so I would like to have a shorter
way to say it.
"F3" has a limited (@4) different possibilities, but the "OR" combinations &
the goto's are quite numerous.
Thanks for any help!
Nick

HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN CELL
 

possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

example:
IF(OR(F3="BD",F3="CD"),B6,C6)

if cell "F3" is a certain value my formula requires different cell values,
this may occur numerous times in a formula, so I would like to have a shorter
way to say it.
"F3" has a limited (@4) different possibilities, but the "OR" combinations &
the goto's are quite numerous.
Thanks for any help!
Nick

HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN CELL
 

One way would be to use the =INDIRECT() and =VLOOKUP() functions. If yo
put the possibilities for F3s value in, for example, column G and th
address of the cell to relate that to in column H (i.e. to duplicat
your folrmula G1 = "BD", H1 = "B6", G2 = "CD", H2 = "C6") then use th
formula:

=INDIRECT(VLOOKUP(F3,G:H,2,0),TRUE)

The vlookup finds the value in F3 from the list in G and returns th
appropriate address from H, and the indirect takes that address an
returns the value. The formula will return #N/A if F3 isn't found i
G.

Hope this helps
Co

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=54472

HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN C
 

tom,
could you give an example
Nick

"Tom Ogilvy" wrote:

possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

example:
IF(OR(F3="BD",F3="CD"),B6,C6)

if cell "F3" is a certain value my formula requires different cell values,
this may occur numerous times in a formula, so I would like to have a shorter
way to say it.
"F3" has a limited (@4) different possibilities, but the "OR" combinations &
the goto's are quite numerous.
Thanks for any help!
Nick

HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN C
 

Insert=Name=Define

Name: MyCell

RefersTo:
=INDIRECT(IF(OR(Sheet1!$F$3="BD",Sheet1!$F$3="CD") ,"Sheet1!B6","sheet1!C6"))

then in your formula (for example)

=(21^mycell+mycell)/(3+rnd()*mycell)

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

tom,
could you give an example
Nick

"Tom Ogilvy" wrote:

possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

example:
IF(OR(F3="BD",F3="CD"),B6,C6)

if cell "F3" is a certain value my formula requires different cell values,
this may occur numerous times in a formula, so I would like to have a shorter
way to say it.
"F3" has a limited (@4) different possibilities, but the "OR" combinations &
the goto's are quite numerous.
Thanks for any help!
Nick

HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN C
 

Tom & Col,
I'm not seeing how to apply this :(

my "IF"'s could be any combo i.e.
IF(OR(F3="BD",F3="CD"),B6,C6)
IF(OR(F3="BW",F3="CD"),H6,G6)
IF(OR(F3="BW",F3="CW"),B9,C1)
with various "then" and "else" responses
they are not in a "LOOKUP" format

sorry for being obtuse!!
Nick


"Tom Ogilvy" wrote:

Insert=Name=Define

Name: MyCell

RefersTo:
=INDIRECT(IF(OR(Sheet1!$F$3="BD",Sheet1!$F$3="CD") ,"Sheet1!B6","sheet1!C6"))

then in your formula (for example)

=(21^mycell+mycell)/(3+rnd()*mycell)

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

tom,
could you give an example
Nick

"Tom Ogilvy" wrote:

possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

example:
IF(OR(F3="BD",F3="CD"),B6,C6)

if cell "F3" is a certain value my formula requires different cell values,
this may occur numerous times in a formula, so I would like to have a shorter
way to say it.
"F3" has a limited (@4) different possibilities, but the "OR" combinations &
the goto's are quite numerous.
Thanks for any help!
Nick

HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN C
 

If there are multiple random variations with no rhyme or reason, then I guess
you need to write them out.

--
regards,
Tom Ogilvy


"100pinesfarm" wrote:

Tom & Col,
I'm not seeing how to apply this :(

my "IF"'s could be any combo i.e.
IF(OR(F3="BD",F3="CD"),B6,C6)
IF(OR(F3="BW",F3="CD"),H6,G6)
IF(OR(F3="BW",F3="CW"),B9,C1)
with various "then" and "else" responses
they are not in a "LOOKUP" format

sorry for being obtuse!!
Nick


"Tom Ogilvy" wrote:

Insert=Name=Define

Name: MyCell

RefersTo:
=INDIRECT(IF(OR(Sheet1!$F$3="BD",Sheet1!$F$3="CD") ,"Sheet1!B6","sheet1!C6"))

then in your formula (for example)

=(21^mycell+mycell)/(3+rnd()*mycell)

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

tom,
could you give an example
Nick

"Tom Ogilvy" wrote:

possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

example:
IF(OR(F3="BD",F3="CD"),B6,C6)

if cell "F3" is a certain value my formula requires different cell values,
this may occur numerous times in a formula, so I would like to have a shorter
way to say it.
"F3" has a limited (@4) different possibilities, but the "OR" combinations &
the goto's are quite numerous.
Thanks for any help!
Nick

HOW DO I GO TO DIFFERENT CELLS BASED ON WHAT IS IN A CERTAIN C
 

thanks Tom
Nick

"Tom Ogilvy" wrote:

If there are multiple random variations with no rhyme or reason, then I guess
you need to write them out.

--
regards,
Tom Ogilvy


"100pinesfarm" wrote:

Tom & Col,
I'm not seeing how to apply this :(

my "IF"'s could be any combo i.e.
IF(OR(F3="BD",F3="CD"),B6,C6)
IF(OR(F3="BW",F3="CD"),H6,G6)
IF(OR(F3="BW",F3="CW"),B9,C1)
with various "then" and "else" responses
they are not in a "LOOKUP" format

sorry for being obtuse!!
Nick


"Tom Ogilvy" wrote:

Insert=Name=Define

Name: MyCell

RefersTo:
=INDIRECT(IF(OR(Sheet1!$F$3="BD",Sheet1!$F$3="CD") ,"Sheet1!B6","sheet1!C6"))

then in your formula (for example)

=(21^mycell+mycell)/(3+rnd()*mycell)

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

tom,
could you give an example
Nick

"Tom Ogilvy" wrote:

possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.

--
Regards,
Tom Ogilvy


"100pinesfarm" wrote:

example:
IF(OR(F3="BD",F3="CD"),B6,C6)

if cell "F3" is a certain value my formula requires different cell values,
this may occur numerous times in a formula, so I would like to have a shorter
way to say it.
"F3" has a limited (@4) different possibilities, but the "OR" combinations &
the goto's are quite numerous.
Thanks for any help!
Nick