# Conditional Formatting

#### beddy2

##### New Member
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!

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the board.

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

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

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 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..

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

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

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
ABCD
18
23
3
41
52
63
74
85
96
107
118
129
1310

</tbody>
Sheet1

Worksheet Formulas
CellFormula
A2=CHOOSE(A1,C13,C12,C11,C10,C9,C8,C7,C6,C5,C4)

</tbody>

<tbody>
</tbody>

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!" ?

Which sheets are C95, C24, and C14 on?

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"

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.

Last edited:
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)

Replies
18
Views
340
Replies
1
Views
438
Replies
1
Views
272
Replies
8
Views
185
Replies
1
Views
77

1,203,464
Messages
6,055,571
Members
444,799
Latest member
CraigCrowhurst

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back