conditional formatting #14

Hello, i have a question on conditional formatting.
I am using Excel 2003.
if i try to use a condition that references another worksheet, it gives an
error.
"you may not use references to other worksheets or workbooks for conditional
formatting criteria"
why do they do that?
is the only thing i can do is create a bunch of hidden cells to my other
worksheet ("=<other sheet>!<other cell>")
then reference thoes hidden cells on my condition?
if i can do this, why can't i just go straight to the other sheet?

thanks


0
greg4709 (89)
9/28/2004 8:37:47 PM
excel 39879 articles. 2 followers. Follow

3 Replies
211 Views

Similar Articles

[PageSpeed] 16

Hi
you can define a name for the other cells and use this defined name
('Insert - Name - Define') in the conditional format dialog

--
Regards
Frank Kabel
Frankfurt, Germany

"greg" <greg@nospam.com> schrieb im Newsbeitrag
news:#kFDssZpEHA.3428@TK2MSFTNGP11.phx.gbl...
> Hello, i have a question on conditional formatting.
> I am using Excel 2003.
> if i try to use a condition that references another worksheet, it
gives an
> error.
> "you may not use references to other worksheets or workbooks for
conditional
> formatting criteria"
> why do they do that?
> is the only thing i can do is create a bunch of hidden cells to my
other
> worksheet ("=<other sheet>!<other cell>")
> then reference thoes hidden cells on my condition?
> if i can do this, why can't i just go straight to the other sheet?
>
> thanks
>
>

0
frank.kabel (11126)
9/28/2004 8:41:24 PM
You can define a name that is a reference to the other 
sheet. For example, if you want to color A1 on Sheet1 if 
A2 on Sheet2 is greater than 2:

1) Press Ctrl+F3.
2) In "Refers to:" put:
        =Sheet2!$A$2
   and give it the name "myrng" (no quotes).
3. Now go into conditional formatting and use:
        =myrng>2

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hello, i have a question on conditional formatting.
>I am using Excel 2003.
>if i try to use a condition that references another 
worksheet, it gives an
>error.
>"you may not use references to other worksheets or 
workbooks for conditional
>formatting criteria"
>why do they do that?
>is the only thing i can do is create a bunch of hidden 
cells to my other
>worksheet ("=<other sheet>!<other cell>")
>then reference thoes hidden cells on my condition?
>if i can do this, why can't i just go straight to the 
other sheet?
>
>thanks
>
>
>.
>
0
jason.morin (561)
9/28/2004 8:49:12 PM
thanks for the suggestions.  i will give it a try


"greg" <greg@nospam.com> wrote in message
news:%23kFDssZpEHA.3428@TK2MSFTNGP11.phx.gbl...
> Hello, i have a question on conditional formatting.
> I am using Excel 2003.
> if i try to use a condition that references another worksheet, it gives an
> error.
> "you may not use references to other worksheets or workbooks for
conditional
> formatting criteria"
> why do they do that?
> is the only thing i can do is create a bunch of hidden cells to my other
> worksheet ("=<other sheet>!<other cell>")
> then reference thoes hidden cells on my condition?
> if i can do this, why can't i just go straight to the other sheet?
>
> thanks
>
>


0
greg4709 (89)
9/28/2004 9:11:57 PM
Reply:

Similar Artilces:

Conditional formatting date
Xl 2000 user. We have a vacation template with the day of the month listed as (1,2,3,etc) starting in A6. The months are column headings starting in row 4 and are date formats listed as 12/1/03,1/1/04, etc. I set up conditional formats to change the patterns for weekend days. Those are : =WEEKDAY(DATE(YEAR(AK$4),MONTH(AK$4),$A36))=1 =WEEKDAY(DATE(YEAR(AK$4),MONTH(AK$4),$A36))=7 I tried to set up a third condition that will gray the incorrect days (i.e. November 31st) =MONTH(DATE(YEAR(AK$4),MONTH(AK$4),$A36))>MONTH(AK$4) This works for November, but does not work for any month with ...

Formatting Problem
We are trying to be consistent in the way we format our phone numbers, addresses, etc. in CRM. When some of our users enter a phone number like 717-123-4567, CRM will change it to (717) 123-4567 after the record is saved. This only happens on select computers. Does anyone have an idea what is affecting the formatting in CRM? Thanks. are these users working on the web client? -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Tom Gwinn" <tgwinn@all-health.com> wrote in message news:ekYjT5QFFHA.4072@TK2MSFTNGP10.phx.gbl... > We are trying to be consiste...

How to retrieve old email after formatting pc
since windows vista infected virus that crash the windows system, i format my laptop and use windows 7. But i forget to backup my ms outlook 2007 email. Can anyone expert in here give an idea or any software that can retrieve back all my email, because it really important email its related with my work. for your info i use company email that out souce to webmail provider. Thank You. Do you have an older backup? Were copies of the mail stored on the server? Recovering it after a reformat is very tricky (and expensive) business and there's no guarantee it'll even work. ...

Questions on format of Select statement
SELECT tblCalls.ColTime, tblCalls.colProblem, tblCalls.colResolution from tblCalls, in dbCallLogs WHERE tblCalls.colResolution from tblCalls, in dbCallLogs where tblCalls.ColTime >= #12/1/2006# <= #3/2/2007# ORDER BY tblCalls.ColTime DESC; I want to know if this is right? I notice in some queries the table name before the column name is missing? Also when do you have to put in the database name? Is it the same in Access as SQL Server? I want the most recent first. I want those records betweeb 12.1.06 and 3.01.07. thanks, On Wed, 21 Mar 2007 13:30:07 -0700, Janis <Janis...

Format cell will not appear
Hello, I have a budget spreadsheet in E2k that I need to update. I can change al the numbers and formlas but if I select Format Cells the dialog box will not appear. I cannot find a single cell anyware in that workbook that works. The budget sheet has some grey area to the righ. I tweaked it last year and I can't remember what I did. Please help Tim Tim, Maybe the sheet is protected. Tools, Protection. If you see Unprotect sheet" it was. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Tim Marciniak" <t...

Default date format #2
I have Excel 2010 (32-bit) in 64-bit Windows 7 Home Premium. My regional and language settings in Windows have yyyy-MM-dd set in the short date format. Lately, when I enter a date in Excel, it comes up in the form 19-Jan instead of 2012-01-19. I know how to change it, of course, but it's annoying to have to change the format every time I enter a date. I've looked through the options, but I'm not finding the relevant setting. How do I tell Excel 2010 that its default format for dates should match the format set in Windows, which is 2012-01-19? -- Stan Brown, Oak Road...

Help to add Format to VBA Code!
-- After searching Google.groups.com and finding no answer, Bob Vance asked: Can "CopyFormat" be added to this code Sub MonthPlus() With ActiveSheet.Buttons(Application.Caller).TopLeftCell If .Column > 8 Then Cells(.Row - 43, .Column - 8).Select ActiveCell.FormulaR1C1 = "=R[-2]C+30" End With End Sub Thanks in advance.........Bob Vance Activecell.numberformat = "mm/dd/yyyy" (use the format of your choice) Bob wrote: > > -- > After searching Google.groups.com and finding no answer, Bob Vance asked: > Can "CopyFormat" be added to t...

Strange character on sheet prevents correct formatting
I have a spreadsheet in 2000, that has the ` character, right in front of all the text in every cell. I know this is a non-showing character. When I try to change my date format from xx/xx/xx to xx/xx/xxxx this character prevents this from happening. If I delete this either in the cell itself or the formula bar the format will then change My problem is I have another spreadsheet that has this about 500 times and so far I have only been able to do it manually. Can anyone tell me how to remove this on the entire sheet? The only thing I haven't tried is the "clean" command...

Phone # Format
Can somebody help me with applying a format to the phone field. the default format seems to be ######### and I am looking to use (###) ###-#### Thanks in advance Dennis, I think it isn't possible at the moment. You could use the sdk and try to use postcallouts which call a webservice which puts the data in a special format and sends it back. Steffen "Dennis" <dennis_ess@hotmail.com> schrieb im Newsbeitrag news:uFrmFMGNEHA.3400@TK2MSFTNGP09.phx.gbl... > Can somebody help me with applying a format to the phone field. the default > format seems to be ######### and...

Cell colour format according to date
If a cell value is equal to today I want the colour to change to red. I have entered =TODAY as a conditional formatting value but the cell colour does not change. The cell has been formatted as ddd dd mmm. Any ideas please. Thanks in advance for any help John Hi John Try the below 1. Select the cell/Range (say A1:A10). Please note that the cell reference A1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Form...

V-lookup and format
Hey, I am making a dashboard for work and am using lots of v-lookups t access a spreadsheet with lots of departmental information. I have th v-lookups for the data all figured out, my question is this: Is there a easy way to bring the format with the data. for instance some of th numbers are just numbers, others are percentages, thus they come to th new cell as .23 instead of 23%. Below is an example of the v-lookup i a using. =VLOOKUP($B22,'[People Hub.xls]people hub'!$E$9:$R$5000,'[Peopl Hub.xls]people hub'!F$5,FALSE) I don't want to preset the cells format in the da...

Date Format #22
The digits typed do not match the output, I wonder why. I also tried to type 000000 and that defaulted to Jan1 1900. then I input 010101 which results into 8/27/27 , for 8/27/1927. this really baffles me, as I tried to Clear All to no avail. thanks for your help I'm having the same problem. 12-17-04 comes in as 03-18-33, 12-14-03 = 08-25-29. IF I enter the same date again, the same wrong date is in the column. I have repeatedly reformatted with no success. Also checked the help site...no help there either. Any ideas? Someone please help ASAP "itqueencanada" wrote: ...

conditional format multiple colours #2
not sure i understand about testing for a string? I undersand about using condtional fomat for 4 values , but I have possiblty of 8 Joh -- atme ----------------------------------------------------------------------- atmel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1615 View this thread: http://www.excelforum.com/showthread.php?threadid=27586 John, I mean that I am reading that the cell will contain say '0800 - 15:00', not a time value, so you test for a string not a number. If you have 8 it is probably best to use worksheet events. Here is an ...

Conditional Formats, how to scroll and view all formats?
Good day! Is there an easy way to scroll over cells and see the conditional formats without individually going through 'Format' and 'Conditional Formatting' cell-by-cell? I'm working with a rather large worksheet and have found errors in the conditional formatting of some cells. Depending upon the category, the format needs are different. Thanks! -Bill ...

Conditional Formatting and Borders
I'm trying to add thick borderlines on my spreadsheet using conditional formatting. But the "thick" option is not available. I observed the code using the macro recorder which produced Sub ConditionalFormat1() Range("A5:O428").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5<>$A6" With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 3 End With End Sub .... I thought I could chagne the ...

Save graph formatting
I have formatted my pivot graph but every time I refresh or change the pivot, the graph reverts back to a different default. I have saved it as a default graph. Have you tried unclicking the checkbox for 'AutoFormat table' in the PivotTable Options window? -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Teresa" wrote: > I have formatted my pivot graph but every time I refresh or change the pivot, > the graph reverts back to a different default. I have saved it as a default >...

File format problems!
Hi all. I have created a poster in publisher which contains both images and text. However, when I save this in any format other than a publisher file, it appears blurred and disported. (The same happens in I copy and paste it into another programe.) What can I do? I need to change the format in order to send the file. Mny Help would be appreciated. Eddy Eliaz <eddyeliaz@hotmail.com> was recently heard to utter: > Hi all. I have created a poster in publisher which > contains both images and text. However, when I save this > in any format other than a publisher file,...

Format numbres to 32nds
I download prices from quote service on 30 Treasury Bond futures. They are downloaded as i.e. 11320, meaning 113 20/32. When using the original format in a formula it treats it as a whole number. How can I convert this original format to be treated as 32nds. =INT(A1/100)+(MOD(A1,100)/32) Format Custom as "# ?/32" if you like -- Kind regards, Niek Otten "martinkn" <martinkn@discussions.microsoft.com> wrote in message news:E6BA90ED-A3CA-4438-B08B-D94AC6ED9A93@microsoft.com... >I download prices from quote service on 30 Treasury Bond futures. They ar...

Global fix for scientific number format?
Does anyone know if there is a way to globally change the format of a number that displays as a scientific number? For example, I have a column of numbers that are being displayed like "0348489+E", etc... If I change the format to text, it still doesn't help. I have tried the apostrophe before the number and it does seem to change it to the number that I need it to be, however, I have about 10,000 numbers that I need to put the apostrophe in front of and can't possibly do it to each one individually. Is there any way to do this to all of the numbers at once? ...

no longer can change width height in format picture
I can no longer can change width and height (overtyping) in format picture. I am using Word 2003 and have a image in front of me. Could do it previously but reloaded my program after replacing hard drive? help ...

using conditional formatting with dates
I have a spreadsheet using excel 2003. I need the row to be one color if a box is filled in with specific text. I have that. I now have 2 conditions left to use. I need the row to turn one color when the date in the box is between now and 30 days from now. Then I need it to turn another color when that same date in the box is past. I think i have the coloring down but my blank cells change color. is there any way to avoid this? For your third condition you are probably using a CF formula like this: =3Dcell<NOW() You can change this to: =3DAND(cell<NOW(),cell<>...

Help, with formatting issue?
Under Outlook in tool/options / mail format, if I have it set to plain text and I create new mail, I do not have the ability to select formatting options. Try it, it shows up under customize ok, as soon as you select ok under customize, it blanks out. If I choose to use Microsoft Outlook Rich Text, I can't send some attachments to Outlook express users. Any one able to confirm this? Its driving me nuts.... -- Greg Eshleman ETEMCO 1370 Arcadia Road Lancaster, PA. 17601 717-393-9653 http://www.etemco.net geshlema@etemco.net What about using HTML format instead? It's open st...

open a 2002 file format with Access 2000
My database was created from Access 2000 and then was converted in Access 2002 format. My german colleague has only the Access 2000 software, so she obtained the following message "this database shows an unknown format, it has probably been created with a later version of access, you should update your access version". The problem is that, even after the use of the Access tool "converting into the previous version", she gets the message "could not create the database window, please update to a new version of internet explorer". I don't see what IE has ...

Cell Format
Would it be possible to limit the number and format of the characters entered into a cell? For example XXX 12345 /XX. As you typed the X's and numbers would be replaced and the "?' preserved. Maintaining the spacing is what is most important. Excel doesn't have an input mask feature similar to the one in Access. You could use three cells for data entry, and use data validation to control the data in each. Then, use a formula in another cell, to combine the data and the necessary spacing. ANDREW MC wrote: > Would it be possible to limit the number and format of the ch...

PLZ Help with Excel cell format or cut/paste problem
I have created a NBA game scheduler and I did copy and pasted the info from the NBA web site. So it is about 3 pages of signal lines. Some of the columns are all single rows across but some of them end up being as discribbed below. Whenever I want to copy/cut one of the cells to another cell it will then take up 2 places. Meaning if I copy something ---------- | | like this, it will paste ---------- ---------- | | like this | | ---------- If I cut and paste the whole row that looks like this to start out ---------- -------------- ---------- ------...