| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| fill a cell based on another cells value | Excel Worksheet Functions | |||
| cell value based on another cells formula??? | Excel Discussion (Misc queries) | |||
| Formatting a cell based on another cells value | Excel Discussion (Misc queries) | |||
| protect cells based on another cell | Excel Worksheet Functions | |||
| sum cells based on another cell value | Excel Worksheet Functions | |||
Linear Mode
