Conditional formatting with reference to other worksheets ?

Hi all,

I am trying to set a conditional formatting using a formula which refers to a cell on another worksheet but thats not working, any better way ?

Thank you much .


Hello

I need to colour the Cell A1, A2 and so on in reference to the Cell Value in Cell B1, B2 and so on.

For example if i have the word "urgent" in cell B1, the cell A1 has to get a red colour. If i have the word "uninteressting" in cell B2, the cell A2 has to get a green colour.

How can i get this? Do I need to use a makro or is it possible with a conditional formating with reference to another cell?

Greets

Hi,

sorry to bother you with another question on conditional formatting, but I couldn't find an answer in one of the many threads about conditional formatting in this forum.

I have a table, in which every cell should be formatted depending its own value and the value of a reference cell in another worksheet (this reference cell is a different one for each single cell to be formatted). Depending on the value of this reference cell (>1 or

Hello,

I have made a sample file with conditional formatting. The file contains 3 sheets which contain a number of orders. Conditional formatting is changing colour of the whole line depending on value in column C. The formatting contains reference to a certain cell in column C. It is very easy to make when the lists are so short: I make conditional formatting for line 2 with reference to cell C2, copy formats in the other 2 lines and change formula references to C3 and C4. However in the original spreadsheet there might be more than 5000 lines per sheet and it will be too time consuming to correct the formulas manually. Does anyone know how I can make a reference to column C without specifying each particular cell?

Thank you in advance.
Anna.

I want to find deviations in a data set of supposed repeating incrementing
values (00, 01, 02, ..., 253, 254, 255, 00, 01, 02 ...), in rows of 4 by 64
values, one value per cell.. I want to highlight where the incrementing
pattern is interrupted. So I do a conditional format where my Condition1 is
Formula is =B1-A1=1. That would be fine except, the gotcha is my values
aren't decimal, they are hex (00, 01, 02, ... FD, FE, FF). So I tried
Condition1 is Formula is =hex2dec(B1)-hex2dec(A1)=1.

I get this error: You may not use references to other worksheets or
workbooks for Conditional Formatting criteria.

I can do enter formulas in cells with the hex2dec(A1), but not the
conditional formatting.
thanks in advance.

Hi All,
I want to create a formula:
ActiveCell.FormulaR1C1 = "=SUM('01'!RC[-54],'02'!RC[-54],'03'!RC[-54])"
where '01', '02', '03' are worksheet names in the same workbook. Recording a
macro gives the same reference format to other sheets in the workbook. If I
create this formula manually, it works after re-opening the workbook.

However, if I create the formula with the line above, Excel interprets the
'01'!, '02!', '03!' references as those to other workBOOKS instead of
workSHEETS. It informs me that the workbook contains references to other
workbooks, and it asks me if I want to refresh ... .
What's wrong? How can I create a workSHEET reference with
ActiveCell.FormulaR1C1?

Thanks!
Stefi

Hello!

I've trying to apply conditional formatting to a column but not succeeded. I get the message "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

Conditional formatting.xlsx

Could someone help me?

Thanks

Hello everyone.

I'm trying in Excel 2007 to format cells in one sheet (e.g. Sheet2!A1:Z3) based on the the values of another sheet (e.g. Sheet1!A1:Z3). In particular, I want if values in Sheet1!A1:Z3 are > 0 to apply a certain colour to the corresponding cells in Sheet2!A1:Z3.

I tried assigning values in Sheet1!A1:Z3 a name (e.g. table) and then applying the conditional formating =table>0 in Sheet2 but it doesn't seem to work.

I also tried using the INDIRECT function =INDIRECT("Sheet1!$A$1:$Z$3")>0 but this doesn't seem to work either.

Does anyone have a workaround for this? Thanks!

Dear friends

With reference to table below i managed to transform Columns A (id),B(date),C(rectype) to E(id),F(date),G(rectype). Now i would like to create column H(gender) according to what i have in column D (gender). I want a function to give me for each id in column E the corresponding type of gender in column H by looking at the corresponding columns of A and D. In other words i need a function in column H to give me the result illustrated below. Essentially it matches the id variable in columns A and E and give the corresponding gender in column H with reference to column D.

******** ******************** ************************************************************************>Microsoft Excel - Mrexcelxls.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGH1iddaterectypegenderiddaterectypegender2116-Oct-005M116-Oct-005M3111-Oct-013M111-Oct-013M418-Nov-015M18-Nov-015M5120-Jan-024M120-Jan-024M6224-Jul-022F101M7214-Nov-021F102M826-May-033F224-Jul-022F9 214-Nov-021F10 26-May-033F11 204F12 205FSheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Is there any function to make column H automaticaly since my sample is quite big?

Thank you very much!!!

Aris

I have tried to create a drop-down list in Excel, with references in another
workbook than the one where I wanted to input the data from the list, using
the Data Validation function. Reading the Excel help, I've learned that in
order to do that I have o define a Name (Insert-->Name-->Define) for the list
in my reference workbook and when validating the data in my destination
workbook, in the Data Validation dialog window, on the Settings tab, in the
Source Box, I have to insert (preceded by an = sign) the Name defined
previously. Unfortunately, although I followed all the steps as described in
the Help item, I keep on getting the following Error Message: "You may not
use references to other worksheets or wokbooks for Data Validation criteria."
and I cannot validate the data.

Can anybody help me with this? Please write to

I have a cell in a worksheet that is will have two types of inputs: numeric
range from 3,000 to 150,000, and also the word "hourly"

I have conditionally formatted this cell to highlight when the value is
10,000 or greater. the conditional formatting also extends to other cells in
the same row to be highlighted as well.

my problem is that when i enter the text "hourly", the conditional
formatting is triggered and all the cells are highlited. i do not want that
to happen.

any ideas are greatly appreciated.

i think excel is evaluating the word to large number, but i am not sure.

I have a cell in a worksheet that is will have two types of inputs: numeric
range from 3,000 to 150,000, and also the word "hourly"

I have conditionally formatted this cell to highlight when the value is
10,000 or greater. the conditional formatting also extends to other cells in
the same row to be highlighted as well.

my problem is that when i enter the text "hourly", the conditional
formatting is triggered and all the cells are highlited. i do not want that
to happen.

any ideas are greatly appreciated.

i think excel is evaluating the word to large number, but i am not sure.

I have tried to create a drop-down list in Excel, with references in another
workbook than the one where I wanted to input the data from the list, using
the Data Validation function. Reading the Excel help, I've learned that in
order to do that I have o define a Name (Insert-->Name-->Define) for the list
in my reference workbook and when validating the data in my destination
workbook, in the Data Validation dialog window, on the Settings tab, in the
Source Box, I have to insert (preceded by an = sign) the Name defined
previously. Unfortunately, although I followed all the steps as described in
the Help item, I keep on getting the following Error Message: "You may not
use references to other worksheets or wokbooks for Data Validation criteria."
and I cannot validate the data.

Can anybody help me with this? Please write to cezar.dumitriu@businesscope.ro

Greetings,

What I'm trying to create is a In-cell dropdown box that shows different options depending on the value of a different cell.

My method of creating this conditional Data Validation is to use the IF function:


However, since my source cells for the Data Validation is on a different worksheet, I get this error message when I try use the above formula:

"You cannot use references to other worksheets or workbooks for Data Validation criteria."

To double check if that was the only problem, I entered the same formula but referenced cells on the same sheet:


And it works perfectly.

What's interesting though, is that if I don't use the IF function at all, I can reference cells in different worksheets just fine.

So I suppose I have two questions:

1. Why is it I can't use sources from another Worksheet for Data Validation if I'm also incorporating an IF statement, but I can without it?
2. Is there another method for me to achieve my goal? Basically I want different choices to be made available in the dropdown box depending on what the value is in a different cell.

I'm using Excel 2007.

Thank you!

I have developed a 400-worksheet workbook and during its evolution I have
copied some cells from other worksheets. This has created references to other
worksheets that no longer exist, so whenever I open the workbook I get the
annoying messages relating to "links not found". I am using Excel 2002. When
I select Edit/Links then it shows the offending 4 non-existent spreadsheets
but I cannot locate the cells that reference them. When I use the Edit/Find
and select Options/Within-Workbook to try to locate the spreadsheet
references to these non-existent spreadsheets, no cell references are located
by the "Find".
Can anyone suggest how I can locate these "phantom" references and get rid
of them???
--
Thanks, Richard.

Hi,

I have a formula =CORREL(INDIRECT($E$1&B6):INDIRECT($E$1&$B$5),INDIRECT(Benchmarks!AG1&Benchmarks!B6):INDIRECT(Benchmarks!AG1&Benchmarks!$B$5))

where the first reference inside each indirect is the column number, and second is row number.

The second array is on another worksheet called benchmarks

It keeps returning a ridiculously small number which I know is wrong after doing a manual calculation.

I have not posted the spreadsheet as it is so big - does anyone know if using indirect in correl with references to other worksheets is doable?

Hi,

I need to create conditional formatting on a worksheet where I need to change the cell colour based on 6 cases.

The VBA that I have found does not work with referenced cells to another worksheet in the workbook which I need as all the data is referenced with calculations.

I also need to specify the cells that the VBA will apply to as I need to apply 6 or 7 different different sets of conditional formatting on the same worksheet to different groups of cells.

Any help will be appretiated!

Thanks,
Cameron

I know that for conditional formatting the criteria cannot include references to other worksheets even if they are in the same workbook, but does somebody have a workaround for this? Thanks in advance.

I currently have a week summary sheet (that has conditional formatting) and also several other sheets with the same data broken up into days. Obviously the days copy the data directly from the summary sheet, however is there a way to copy the conditional formatting. I currently have a function that can return the number of the conditional format that a particular cell passed, however can not pass a different worksheet through the conditional formatting. I've tried the indirect function but to no end.

EG:
Summary sheet:
Object Mon Tues
A 2 10
B 7 8
C 3 5

Cell A2 is blue/bold due to conditional format rule "2".

Monday Sheet:
Object Number
='Summary'!A2 ='Summary'!B2

I've tried conditional format for B2 as (using my CondNumber function):
=CondNumber('Summary'!B2)=2 .... then format to blue

This is where i get the 'references to other worksheets' error ... any ideas???

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I am experiencing a problem with Conditional Formatting. I am trying to format a Column based upon a cell value being even. When I make the conditional format, FORMULA IS = ISEVEN($A3), i am getting the error "You may not use references to other worksheets or workbooks for Conditional Formatting criteria"

Has anyone used ISEVEN in conditional formatting at all?

Example: I have a workbook with 3 sheets: Report, DeptA, and DeptB. When I
change the value in the cell named Dept in Report to "A", I want the other
cells in Report to retrieve values from DeptA spreadsheet, and when I enter
Dept="B" I want the values from the DeptB spreadsheet.

In other words, I want the reference to the other worksheets to change based
on the value in one cell. Can I do this by combining formulas or do I have to
use VBA?

I tried something like [="Dept"&(dept)&!A1] (if A1 was the cell I wanted),
but got problems combining text and formula. I know I could use IF, but not
if there are 20 different sheets. Besides, shouldn't there be a more elegant
way to do this?

btw, I learned that one from Chip.

http://www.cpearson.com/excel/DateTi...tm#DaysInMonth

"Jonathan Cooper" wrote:

> My guess is that the problem is becuase the EOMONTH formula is part of the
> analysis tool pack.
>
> I worked around it with the following conditional format formula in F14
>
> =IF(DATE(YEAR(L3),MONTH(L3)+1,0)=F14,TRUE,FALSE)
>
>
>
> "MeatLightning" wrote:
>
> > Hey all -
> > I'm trying to use the following formula to conditionally format a cell:
> >
> > =EOMONTH(F14,0)=L3
> >
> > I basically have a cell in my sheet with "TODAY()" then I have series of
> > cells (headers for columns G thru AH) that create a timeline based on today's
> > date using the "EOMONTH" function / formula. For example: Cell "G3" has
> > "=EOMONTH(A3,-3)", Cell H3 has "=EOMONTH(A3,-2)" etc.
> >
> > I then have a series of tasks in column A. Each with an end date entered as
> > "m/dd/yy". I'd like to indicate when a given task is due by formatting the
> > background color of the appropriate cell.
> >
> > When I enter =EOMONTH(F14,0)=L3 as the conditional formatting formula, I get
> > an error message saying "You may not use references to other worksheets or
> > workbooks for Conditional Formatting criteria."
> >
> > Any help you could send my way would be much appreicated!
> >

I have consective numbers in column A. In column B is formula =isodd
which returns True or False, and this works for me but I have tried to
use =isodd and =iseven as a conditional format formula with no luck.
My question is, is it possible to use either of these arguments in a
conditional format formula. I have tried severial syntax with no luck.
I most often get error message "May not use referance to other sheet
or book...." I know you guys can crack this.

Regards

BigD

Hello all,

I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):

Formula is: =isodd($A2)

The error I keep getting is this:

"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."

I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.

I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or
two and I want the shading to automatically adjust.

What is going on here? Does anyone know how to get this to work?

--
Thanks for any help anyone can provide,

Conan Kelly