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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.
 
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
 
tom,
could you give an example
Nick

Tom Ogilvy said:
possibly use a defined name with a formula to determine which cell to return.

this might be practical for a small number of formulas.
Click to expand...
 
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)
 
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
 
If there are multiple random variations with no rhyme or reason, then I guess
you need to write them out.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how do i write an if/and/then statement to compare adjacent cells? TIA greatly 7
change cell color when result is above a certain value 4
How to enter a value in a cell but display a different value in the same cell? 4
Change background color based on cell value in vb?? Current code notworking... 1
how do I replace formula with value or cell reference with value ? 4
Combining Text based on Values 8
How to terminate a condition (IF) at its first occurance? 2
How to keep formula and be able to input value in cell? 0

Back
Top