Conditional Formatting

[beddy2]

I would like to have one cell equal another cell depending on what number is typed in.


For example, if A1=10, then I would like A2 to equal cell C4. However, if A1=9, then I would like A2 to equal C5. And so on so forth (I need 10 circumstances)


Please let me know if you need more clarification.

Thank you!

 

[bbott]

Welcome to the board.

You don't need to use conditional formatting. Just enter this formula in A2:

=INDEX(C4:C14,11-A1,1)

 

[beddy2]

Thanks for the help. I'm still not quite getting it. Let me be a little more clear, I didnt realize it was going to be so simple.


I would like cell C95 to equal C24 on a different spreadsheet when cell C14 equals 10.

Then, I would like cell C95 to equal C25 on a different spreadsheet when cell C14 equals 9.

So on and so forth till 1..

Thanks in advance

 

[bbott]

Thanks for the help. I'm still not quite getting it. Let me be a little more clear, I didnt realize it was going to be so simple.


I would like cell C95 to equal C24 on a different spreadsheet when cell C14 equals 10.

Then, I would like cell C95 to equal C25 on a different spreadsheet when cell C14 equals 9.

So on and so forth till 1..

Thanks in advance

Assuming the values in C24:C33 are on Sheet 2, try this in C95:

=INDEX(Sheet2!C24:C33,11-C14,1)

 

[skywriter]

Here's another option, not as nice as bbot's, but if the cells were scattered around it would work. The first cell reference after A1, is the value to return if A1=1, the second if A2=2 and so on.

Excel 2010

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=CHOOSE(A1,C13,C12,C11,C10,C9,C8,C7,C6,C5,C4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[beddy2]

Assuming the values in C24:C33 are on Sheet 2, try this in C95:

=INDEX(Sheet2!C24:C33,11-C14,1)

I have this formula in, however it is resulting in "#REF!" ?

 

[bbott]

Which sheets are C95, C24, and C14 on?

 

[beddy2]

Which sheets are C95, C24, and C14 on?

C95 and C14 are located on sheet labeled "Midtown Input"

C24 is on sheet labeled "Performance Ratio Data"

 

[skywriter]

C95 and C14 are located on sheet labeled "Midtown Input"

C24 is on sheet labeled "Performance Ratio Data"

Best thing to do is go to the cell you want the formula in, type =index( then go to the other sheet and select the cells in the range C24 to C33, then type a comma(,) then type 11- then select C14 on the correct sheet then type a comma(,) then a 1 and ) and hit enter. By selecting the sheets and cells instead of typing sheet names and cells by hand you know there is no syntax error.

Sometimes when we type sheet names we put in an extra space or don't put one where there is one or we mistype the name by one letter and everything looks right, but we get an error and can't figure out why. If you select the sheets and cells Excel does all of that for you automatically.

 

[bbott]

C95 and C14 are located on sheet labeled "Midtown Input"

C24 is on sheet labeled "Performance Ratio Data"

Enter this in C95 on Midtown Input sheet:

=INDEX(Performance Ratio Data!C24:C33,11-C14,1)