Conditional Formatting Formula Help Part 2

Hi,

I am working on a conditional formatting formula that will refer to a
different worksheet that the one it is in, I hope this can be done, so
here's how the formula needs to be:

in cell X2 of worksheet "10 DAY AVERAGES" I need a formula that tests
cell W2 in worksheet "PASTE DATA" to see if it's greater than the 20
day average of cells W2 through D2 in worksheet "PASTE DATA", and if so
I will program a certain highlighting color via conditional formatting,
I would appreciate very much some help with this formula

THANKS!


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=538079

0
5/2/2006 2:45:09 PM
excel 39879 articles. 2 followers. Follow

10 Replies
558 Views

Similar Articles

[PageSpeed] 50

One way:

Name 'PASTE DATA'!W2, say, TheTest
Name 'PASTE DATA'!D2:W2, say, TheRange

In '10 DAY AVERAGES'!X2, choose Data/Conditional Formatting, and set up 
the dropdowns and textboxes to read

CF1:            Formula is   = TheTest > AVERAGE(TheRange)
Format1:        <patterns>/<color>

In article <RalphSE.276q9b_1146581402.1068@excelforum-nospam.com>,
 RalphSE <RalphSE.276q9b_1146581402.1068@excelforum-nospam.com> wrote:

> Hi,
> 
> I am working on a conditional formatting formula that will refer to a
> different worksheet that the one it is in, I hope this can be done, so
> here's how the formula needs to be:
> 
> in cell X2 of worksheet "10 DAY AVERAGES" I need a formula that tests
> cell W2 in worksheet "PASTE DATA" to see if it's greater than the 20
> day average of cells W2 through D2 in worksheet "PASTE DATA", and if so
> I will program a certain highlighting color via conditional formatting,
> I would appreciate very much some help with this formula
> 
> THANKS!
0
jemcgimpsey (6723)
5/2/2006 3:03:32 PM
:( 

bummer, i tried that but it gave me an error message saying you cannot
reference other worksheets in conditional formatting


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=538079

0
5/2/2006 3:42:36 PM
Then you didn't name the ranges and use the names instead of the range 
references...

In article <RalphSE.276ssy_1146584701.8732@excelforum-nospam.com>,
 RalphSE <RalphSE.276ssy_1146584701.8732@excelforum-nospam.com> wrote:

> bummer, i tried that but it gave me an error message saying you cannot
> reference other worksheets in conditional formatting
0
jemcgimpsey (6723)
5/2/2006 4:19:59 PM
not sure what you mean JE, can you try to explain it a little more
clearly please?


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=538079

0
5/2/2006 5:03:03 PM
will this method work if i copy the conditional formatting to other
cells, i.e., will the test cell and range cells change accordingly?


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=538079

0
5/2/2006 5:48:17 PM
You can only reference other sheets in Conditional Formatting by using 
named ranges.

Select your range, then type a name in the name box at the left of the 
formula bar. Hit enter. You may now use that name as a substitute for 
the range reference in a formula.

In article <RalphSE.276wqm_1146589800.9611@excelforum-nospam.com>,
 RalphSE <RalphSE.276wqm_1146589800.9611@excelforum-nospam.com> wrote:

> not sure what you mean JE, can you try to explain it a little more
> clearly please?
0
jemcgimpsey (6723)
5/2/2006 11:09:38 PM
When you directly name a range, the reference won't change.

However, you could use dynamic ranges instead:

    http://cpearson.com/excel/named.htm#Dynamic


In article <RalphSE.276yla_1146592200.4469@excelforum-nospam.com>,
 RalphSE <RalphSE.276yla_1146592200.4469@excelforum-nospam.com> wrote:

> will this method work if i copy the conditional formatting to other
> cells, i.e., will the test cell and range cells change accordingly?
0
jemcgimpsey (6723)
5/2/2006 11:28:13 PM
thanks for the link JE, this stuff is a little over my head, i still
dont get how to name my ranges so they will be dynamic, i'd really
appreciate it if you could explain that in terms of the example I've
given here PLEASE


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=538079

0
5/3/2006 12:59:27 PM
ok then, does ANYONE know how to make the ranges dynamic so that I can
finish this project?  I would really appreciate the help, thanks!


-- 
RalphSE
------------------------------------------------------------------------
RalphSE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29931
View this thread: http://www.excelforum.com/showthread.php?threadid=538079

0
5/3/2006 11:24:47 PM
There's another description here:

   http://www.contextures.com/xlNames01.html

If you're still having trouble, and explain where you're getting stuck, 
someone may be able to help.

RalphSE wrote:
> ok then, does ANYONE know how to make the ranges dynamic so that I can
> finish this project?  I would really appreciate the help, thanks!
> 
> 


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/3/2006 11:45:24 PM
Reply:

Similar Artilces:

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

conditional formatting sort
Is there a way to sort after conditional formatting. So, if I've set up a conditional format to color my text red if the number is between 0 and 95, then I want to sort so all red numbers are at the top of the spreadsheet? Any ideas? Thanks for your time and assistance. Roberta On Thu, 25 Oct 2007 05:08:00 -0700, rrupp <rrupp@discussions.microsoft.com> wrote: You can't sort by color, but you can add a column to your query like this: SortColumn: Iif(SomeValue >= 0 and SomeValue <= 95, 1, 2) Then sort by this column. -Tom. >Is there a way to sort after condition...

Count If Formula #4
I need a formula that will figure the following: If column C = 3, count all times < 00:45 If column C = 4, count all times < 00:45 If column C = 3, count all times > 00:46 Thanks. Your description is a bit confused.You have different actions for the same value (C=3). And where are the times, in a different column than C? So, making many assumptions, perhaps =SUMPRODUCT(--(ISNUMBER(MATCH(C1:C200,{3,4},0))),--(D1:D200<TIME(0,45,0)))+S UMPRODUCT(--(C1:C200=5),--(D1:D200<TIME(0,46,0))) -- HTH RP (remove nothere from the email address if mailing direct) "Denise"...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Please help #8
I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or know how to fix it? Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' "John S" wrote: > > I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or > know how to fix it? > > Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of > memory or system resources. Close some windows or programs and try again.' > >...

Contacts in Exchange #2
We are running Exchange 2003 and Outlook 2003. I have tried to give a user rights to her boss's contacts through delegation and setting the rights on the contacts folder, however, while she has the rights she is unable to add or see her boss's contact folder in her Outlook. She can see it if she runs a search, but can not permanently put it in her list of contact folders. IS there a way that I can accomplish this through the back-end? Thanks, Joseph rapoport jrapoport@insurmark.net ...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

Formula for Game Spreadsheet
I am currenly playing a game of Ars Magica. I am using a spreadsheet to compute the info for my character. I am having a probllem with the casting total for spells related to a paticular character. The Formula is as follows : =IF(H13="","--",IF(J13="",INDIRECT(H13),MIN(INDIRECT(H13),INDIRECT(LEFT(J13,2)),INDIRECT(RIGHT(J13,2))))+IF(K13="",INDIRECT(I13),MIN(INDIRECT(I13),INDIRECT(LEFT(K13,2)),INDIRECT(RIGHT(K13,2))))+Sta+IF(N13="",0,$H$2)) I am trying to compute the casting total for the spells castable by this character. The total for the ...

Goal Seek #2
Can anyone tell me a little about goal seek? Hi basically it's used to change an input variable so that the result of the formula equals what you want it to ... say i have the following A B C 1 10 20 =A1+B1 now C1 will give me 30, but if i want to know what B1 will need to be if i want C1 to show 50, i can use goal seek Set C1 to value of 50 by changing B1 Hope this helps Cheers JulieD "Jamie Hart" <jhart@emaple.net> wrote in message news:u3OscELdEHA.244@TK2MSFTNGP12.phx.gbl... > Can a...

How do I Remove a Split from my Comments in Excel 2003? #2
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off ? ...

Setting a dynamic range in a formula
Hi, I have a column of numbers and I always want the following arra formula to use the last 12 entries: =(PRODUCT(1+D1:D12/100)-1)*100 Any suggestions? Thanks, Phillycheese -- Phillycheese ----------------------------------------------------------------------- Phillycheese5's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2419 View this thread: http://www.excelforum.com/showthread.php?threadid=37809 Assuming that Column D contains no blanks, try... =(PRODUCT(1+OFFSET(D1,MAX(0,COUNTA(D:D)-12),0,12,1)/100)-1)*100 ...confirmed with CONTROL+SHIFT+ENTER. Hope th...

number format with leading 0
Dose anyone know how to create a number format that would show a leading zero for the numbers 0-9. That is, so that 1 would show as 01. I can do with a text format but would like to retain the integrity of the number. Thanks Try this: Format>Cells>Number Category: Custom Type: 00 Click [OK] Does that help? *********** Regards, Ron "Darby" wrote: > Dose anyone know how to create a number format that would show a leading zero > for the numbers 0-9. That is, so that 1 would show as 01. I can do with a > text format but would like to retain the integrity...

Increment A2 from A1 and A2 Sum #2
I have two cells: A1 - manual value B1 - automatic increment = B1 + A1 How can i make it? (=error: circular reference) Note: I have this formula repeated in some lines: = B2 + A2 = B3 + A3 .. -- Message posted from http://www.ExcelForum.com ...

formula or VBA
I need to search through a column checking condtions. If the conditions are met, then I want to enter the corresponding value in the column next to it into a different cell. Basically, I want to search column A and, if the conditions are met, put the value in B into C. Would this be easier via formula or code? And maybe some starting suggestions would be nice if you don't mind. -- Thanks, Jim in a formula in col b =if(cond,a1,"") using vba for each c in range("a1:a100") if cond then c.offset(0,1)=c next -- Don Guillett SalesAid Software donaldb@281.com "...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

ERROR: 5.1.2 or other punctuation after the recipient's email address.
I am trying to send a email to a group address I created with 58 addresses in it. When I try to send the email to this group I get the following Error: "5.1.2 or other punctuation after the recipient's email address. " I have gone back to the original contact cards and made sure the addresses were all legal. I have re-created the list, same problem I have re-booted, same thing. Any thoughts out there? Thanks Dunc My wife and I have MBP's and are both running MS Office for Mac's, Entourage 12.2.3. Both of us have messages cued up in the out boxes th...

Fixing a formula to read a general format?
A few months ago I had asked a question and got this answer: Question: I have a a set of values in A1 through A100. I need to look up each value and find a match in another set of values located in C1 through C200. If a match is found then I need the formula located in column B to return the value in the same row but the next column over (D). Answer: =IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)), VLOOKUP(A1,$C$1:$D$200,2,0), "") Copy down through A100. The formula worked fine, however the reference value and the look up value are not any more numbers but a combinatio...

Converting Formula into Numbers?
With a cell/s selected is there a way to convert the cell's formula into straight numbers (in turn removing all formula and leaving the same number)? -- Emp-Designer ------------------------------------------------------------------------ Emp-Designer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37564 View this thread: http://www.excelforum.com/showthread.php?threadid=571948 Emp-Designer a �crit : > With a cell/s selected is there a way to convert the cell's formula into > straight numbers (in turn removing all formula and leaving the same &...

COUNTIF MULTIPLE CRITERIA #2
To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A Try this: =SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc")) Adjust range references to suit your situation Does that help? *********** Regards, Ron "will A" wrote: > To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. > Thanks > will A =SUMPRODUCT(--(A1:A1000="aa"),--(B1:B1000="bb"),--(C1:c1000="cc")) SUMPRODUCT does not work on a complete column, just a defined range, and all ranges must b...

Calculations adding Text formats
I am new at this so please bear with me. I am using Excel XP. In my column I have a list of numbers. Some are formatted as text and some are formatted at numeric. When I insert by formula it seems to be calculating the entire column, even the text formatted cells. What am I doing wrong. Example Cell A1 = 5 - numeric Cell A2 = 5 - text format Cell A3 = 5 - text format Cell A4 = 5 - numeric format formula states =sum(a1:a4) the total should be 10 not 20. Correct? You're not doing anything wrong, per se. Changing formats cannot change numbers into text values or text values into...