Can I change the color of the text based on a if formula?

[julieduncan]

I have one excel workbook with three sheets in the file. The first sheet contains budget data, the second sheet contains actual data and the third sheet uses if formulas stating if there is an actual number to pull the actual data, but if no actual data exists then to pull the budgeted data. I would like the text to be different color on the 3rd spreadsheet depending on if it is pulling actual or budgeted data. Anyone have any ideas how I can do this? My if statement looks like this on the 3rd sheet - "=IF(Sheet1!A1<>0,Sheet1!A1,Sheet2!A1)"

 

[RoryA]

You can do it with conditional formatting. Which version of excel are you using?

 

[julieduncan]

I'm using Excel 2007.

I've tried conditional formatting depending on which sheet the number was pulling from, but excel will not let me reference another sheet.

 

[RoryA]

You need to create a defined name using name manager (formulas tab) called, say, actual that refers to =INDIRECT("'Sheet1'!RC",0)
and then in the conditional formatting you need to test if the cell value is =actual
Using the defined name allows you to bypass the limitation abbot referring to other sheets.

 

[julieduncan]

Okay I went ahead and named my budget and actual data with the name manager, but I am a bit confused on the indirect formula. Can you provide me more detail?

 

[RoryA]

You need to create a name called actual and in the refersto box enter the formula I gave. That will mean that for any cell that you use =actual in, the reference is to the same cell but on sheet1. You can then test to see whether the cell contains the actual value from the same cell in sheet1, in which case you colour it, or if it differs, you don't colour it. (or vice versa)
Does that make sense?

 

[julieduncan]

Hi, Thank you for your help! It is just what I was searching for. I was able to figure it out today. Do you know if there is a faster way? Currently, I am naming each cell in my actual data sheet and setting up the conditional formatting individually for each cell in the projection sheet. I have one years worth of data I would like to apply this conditional formatting to.

Thank you!

 

[RoryA]

If you are referring to the equivalent cell on the other sheet then you only need one named range. Eg if you format A1:F10 on one sheet based on A1:F10 on the actual sheet, you use the same =actual comparison for all of them.