named ranges in conditional formating inside a pivottable

Hi
(excel 2007)

In my pivottable column k show revenue

Column l show me the sales responsible.

If the revenue is less than e.g. 1,000 I want the name in col I to be
red.

Using conditional formating I refer to the cell k13 (the upper cell i
that column). I would prefer to name that cell "revenue", but I can
not refer to "revenue" in the conditional formating.

What do I do wrong ?

Regards

Peter
0
Peter
2/18/2010 11:40:59 AM
excel 39879 articles. 2 followers. Follow

1 Replies
1490 Views

Similar Articles

[PageSpeed] 37

I just did this in an ordinary sheet but it should work in a PT

Select the Names to format and choose, Conditional Formatting, Choose 
Formula Is,
(In 2007 choose New Rule, based on formula) and Assuming that the list 
begins in A2, enter the formula:

=OFFSET(A2,0,1)<1000

apply your formatting and click OK to exit.

HTH
Peter

"Peter" wrote:

> Hi
> (excel 2007)
> 
> In my pivottable column k show revenue
> 
> Column l show me the sales responsible.
> 
> If the revenue is less than e.g. 1,000 I want the name in col I to be
> red.
> 
> Using conditional formating I refer to the cell k13 (the upper cell i
> that column). I would prefer to name that cell "revenue", but I can
> not refer to "revenue" in the conditional formating.
> 
> What do I do wrong ?
> 
> Regards
> 
> Peter
> .
> 
0
Utf
2/18/2010 4:03:01 PM
Reply:

Similar Artilces:

Chart Names
Hi everyone, I'm sorry if this is an easy one but I just can't figure it out. I have a worksheet with 3 charts on it. I need to write some VBA to select each chart in turn and change its axis settings. So I figured the first thing to do is to name the charts so that I can select them properly in the VBA. But how do I name a chart??? If I select the chart I get "Chart Area" in the name box and I can't edit it. Many thanks for any help, Tony M As answered in .programming -- Hold the Ctrl key and click on the chart to select it. Click in the Name Box, type a new ...

Naming a range
I have a such macro : Private Sub Macro1(arkusz As Worksheet) arkusz.Range(arkusz.PageSetup.PrintArea).Copy Sheets("Sheet1").Select Range("A" & Trim(Str(zLastRow))).Select Arkusz50.Paste Application.CutCopyMode = False ActiveWorkbook.Names.Add Name:="A1", RefersToR1C1:=Selection zLastRow = zLastRow + arkusz.Range(arkusz.PageSetup.PrintArea).Rows.Count Range("A" & Trim(Str(zLastRow))).Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell End Sub However the above macro doesnt work (this part: ActiveWorkbook.Names.Add Name:=&quo...

Name Assignment
I need to assign a staff memeber to a group of clients Staff Names Clients Milk Shoe A-F Form Code G-L John DOe M-P The table that contains the client information CREATE TABLE [dbo].[FD__CLIENTS]( [OP__DOCID] [int] NOT NULL, [Fullname] [varchar](68) NULL, [NameF] [varchar](20) NULL, [NameL] [varchar](20) NULL, [NameMI] [varchar](20) NULL CONSTRAINT [PK_FD__CLIENTS] PRIMARY KEY CLUSTERED There is a staff table but there is nothing that ties it to the clients table. So the staf names will have to be hardcoded. Any ideas? On...

How do I remove the sheet name from a named formula?
I would like to use the same name to refer to the same set of cells on different worksheets: SheetTitle=$A$1 but when I try this it reverts to SheetTitle=Sheet1!$A$1 Any ideas how to do it? I could use =IF(,,,) if I knew how to look up the currently active sheet; =IF(SheetName="Sheet1",TRUE,FALSE) Any ideas how to reference the name of the sheet? Well, you could do something like this: SheetTitle=!$A$1 The exclamation point means that the Name will refer to A1 on the active worksheet. Excel uses the SheetName to tell things apart. If I try to give to different A1'...

Conditional formatting using part of the cells contents
Hi, i'm doing some MPG calculations at the moment and using a formula from a seperate worksheet which then displays on the first worksheet (="You average "&Round(&Sheet2!A4,2)&" MPG") and i want to format that cell (that displays the "Your Average is xx.xx MPG) so that if, for example, my average MPG is <=10 then the whole cell is red, >10 <30 Yellow and >30 Green i hope this makes sense... Cheers for your help Hi, use conditional formating, where the formula is, in the first option enter =A2<=10 choose green 2nd ...

Auto populate Email when Name is chosen
I have a list of names which i present in a combo box on the userform. I have an email textbox. I want the email to be auto-populated based on the name chosed. May I ask your help with this? Hi Jerry, Only the first procedure is really relevant in this post the others are to show how you may want to call it but, that entirely depends on your needs. Private sub BuildAndSend has 4 required parameters “ToWhom”, “Subject”,”Body”, and “Send”. Pass the appropriate values to the procedure and it will send or display an email to the user to send. HTH. Private Sub BuildAndSend( _ ...

Re-post: Conditional sum: different worksheets
Hi ng. Using xl xp pro. Apologies for the re-post. Have not seen reply to my question. Can anyone help me out? tia, goss Orig Post============================================== Tried running conditional sum wizard on tab: Accnt_Dat Through the wizard I established 3 criteria for the sum Wizard prompts for resultant placement. I chose tab: Comm Nothing appeared in the cell I selected. I repeated all steps carefully in case I made an error. Still nothing was placed in: Comm!$C$2. I started trying to type by hand and came up with: =(SUM(OR(IF(Accnt_Dat!$B$2:$B$1700="SALES-1", _ IF...

Custom entity with same display name as out of the box?
I tried to create a new entity "new_InvoiceDDI" with a display name of "Invoice" and it won't allow the display name to be the same as a "out of the box" entity. Why is this? Anyone know a way around it? CRM 4.0 Mark Hi Mark, There's no way around it except renaming the existing invoice entity to something else. One obvious reason is the Outlook Client. It uses the display names to show folders, but folders needs to have a unique name. The web client uses the display names as well, so how can a user differentiate between two entities having the...

Formatting default Entity and Value
In ORM Source documents is it possible to set the default background colour (Fill) of Entity and Value elements? To save me having to continually setting the values manually. Cheers, McGiv Hi McGiv, > In ORM Source documents is it possible to set the default background > colour (Fill) of Entity and Value elements? To save me having to > continually setting the values manually. Sorry, no. The ORM info is separate from the shape info and as such a new shape is created every time you drop a given ORM object on a page. If you want them all to be a given color (which I don't thi...

Populate combobox with folder names
I had found this code to show folder names that displays in a msgbox, is it possible to get the list of folder names into a combobox? --------------------------------------- Sub ListFolders() Dim fs, f, f1, fc, s Dim folderspec folderspec = "C:\Excel\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set fc = f.SubFolders For Each f1 In fc s = s & f1.Name s = s & vbCrLf Next MsgBox s End Sub ---------------------------------------- This is for exc...

Instance name must be the same name as the computer name ...
I've seen many posts about this installation issue and ran across it myself during an install on an existing SBS server. When running Select @@servername I received a null answer. I looked in the sysservers table in the master database and found that there was no '0' server listed as I've seen on all other installs of SQL. I took a chance and changed the value to 0, restarted SQL and ran the @@servername and it now came back as the name of the server as expected and I was able to install CRM with no problems. Long term I don't know if this will cause issues with ...

Change the name of an employee class
Is there a way in the SQL table to change the name of employee classes? Patti, Where did you want to change them (cards, trx, history, etc). There are several tables involved. Would it be possible to make new classes starting with the class you want to duplicate as a default. Please let us know what you are trying to accomplish by changing classes. Kind regards, Leslie "Patti Anderson" wrote: > Is there a way in the SQL table to change the name of employee classes? Our naming convention has changed, and requires changing the names of all employee classes...

"Name Conflict" when Excel 2007 open an old worksheet
Hi all, An "Name Conflict" error message pop-up "Name cannot be the same as a built-in name". That worksheet was created by Excel 2000 and also could open on Excel 2003 without any error message. Change the worksheet file name could not solve the problem after tested. Why? Thank you, Chik "Chik" <c...@netvigator.com> wrote... >An "Name Conflict" error message pop-up "Name cannot be the same as >a built-in name". That worksheet was created by Excel 2000 and also >could open on Excel 2003 without any error message. Change th...

undoing conditional formatting
Hello and great day today. I use conditional formatting to find an error(s) in many rows and columns when my grand totals aren't matching both down and across. I might have 15 columns and 45 rows with data and currency listed. I must make sure each row across matches to a grand total and same down with the columns. The conditional formatting is great for high-liting discrepancies But it also highlites rows that are not really errors when put together with another row. So I fix the row with an actual error but how do I get rid of the highlite on the rows which are not really errors. I can...

Named Reference
Hi, Can someone tell me what the pros and cons are (if any) of these 2 ways of naming a range. ActiveWorkbook.Names.Add NAME:="HOME", RefersTo:=ActiveCell Range("HOME").Select or Dim HOME As Range Set HOME = ActiveCell HOME.Select Thanks, DaveU It seems to me that the first method creates a named range that is essentially meaningless to the user and is only used for VBA processing. Additionally, if you don't write code to delete the named range it will just sit there in the workbook waiting for somebody to ask what it means. The second alternative is a clea...

host A Cdialog inside a CView
Hi, I would like to create a SDI app with a main CView allowing user to add a dialog and after some static and to be able to edit static properties. How can I host a Dialog inside my view ? I want to do a kinf of GUI Editor. You should create the SDI view as a CFormView (you can change in the wizard). That will make it really easy for you. Tom "Vincent RICHOMME" <richom.v@free.fr> wrote in message news:4435926f$0$18986$626a54ce@news.free.fr... > Hi, > > I would like to create a SDI app with a main CView allowing user > to add a dialog and after some static a...

US-UK date formats & downloading transactions
I have accounts in both the US (=base currency) + UK. I typically download transactions manually from the institutions I hold accounts with, but the US-UK date format difference is causing problems (eg. 01/10/05: in the US = Jan 10th, in the UK = Oct 1st). I can't find a way of dealing with this. Help? In microsoft.public.money, "Anthro girl" <Anthro girl@discussions.microsoft.com> wrote: >I have accounts in both the US (=base currency) + UK. I typically download >transactions manually from the institutions I hold accounts with, but the >US-UK date form...

Conditional Formats
I want to set a set of criteria for a row, within a give date range, in a way that as the 'due' date approaches, the cells change color from green to yellow to red. how can i set this up? You use conditional formatting. Depending on how you calculate your 'due' date, you use a condition like: =a1+30>=today() Regards Fred "lau" <lau@discussions.microsoft.com> wrote in message news:3E3F20E9-12F8-4EA5-B430-DCE4E59A70DE@microsoft.com... >I want to set a set of criteria for a row, within a give date range, in a >way > that as the &...

Find a time value in one column based on names in another
Hi - I have a list of names. (40 Different names that may occur 30 - 40 times a piece in column B). In Column C I have a list of times that each person made an entry. Is there a formula that will give me the earliest time that appears in column C for each person on the list in column B? I already have a list of all of the unique names that will appear in column B that I can use as a reference. Example: col A col B col C smith 6:00am jones 5:03pm smith 7:05am jones 4:02pm adams 2:05pm adams 2:33p...

convert from last name, first name TO first name, last name
How can I convert my contacts (as a group menaing all of them at once0 from the last name, first name to first name last name, without having to enter EACH cotnact and perform it almost 400 times? Also, in what format would you suggest trying to print my contacts showing fields name mobile, home, work phone and email address? I assume there is a way to make used defined fields? You'll need to create a post with enough information that others could understand it. How are we to know which field or view you are trying to "convert?" Be specific. I would suggest pri...

Separating a name into first name and last name columns
I have a series of full names in single cells that are formatted the following way: <Last Name>,<Space><First Name> - thus it looks like the following: ADAMS, ROSEMARY I would like to separate this into two columns and eliminate the comma. I appreciate any help. I am working in Microsoft Excel 2000 and need answers in Excel, not in Visual Basic. Thank you, THEFALLGUY David(s) - thank you for the help. Problem solved. The Davids stick together!!!! Thanks, David "THEFALLGUY" wrote: > I have a series of full names in single cells that are formatted the ...

how can i apply conditional formatting by code ?
Say that there is a table T_PERSONAL [Name (text), Surname (text), Salary (integer)]. I created a Tabular Form (F_PERSONAL) which retrieves information from T_PERSONAL. I want to put a image for a specific record on the form, if the record satisfy some specific criteria. For other record which does not satisfy the criteria, image should be invisible. Say that, if [Name (text)] ~text13~ is "ALBERT", Image Object ~Image13~ should be visible. I dont suppose that Image Object should be included in T_PERSONAL. It will be inserted manualy during design stage of Tabular Form. I would not as...

Edit Licensed to Name
I need to edit my last name in the Office 2004 registration (Splash screen). How can I do this? Thanks In article <news-AC4BCA.12232514102004@msnews.microsoft.com>, news@biggerhammer.org wrote: > I need to edit my last name in the Office 2004 registration (Splash > screen). How can I do this? > > Thanks Take a look at http://www.mcgimpsey.com/macoffice/office/pid.html ...

Conditional cell color.
I have used conditional formatting before in order to change the shading of a cell based on the cell value. However, I would like to do the same except have the color change based only on the alpha part of an alphanumeric value. Example: change shading to blue if the alpha = F, and change to yellow if = P (as in: F2409 & P1982). Thank you. Geza, use this formula: =SEARCH("f",A1). If you want it to be case-sensitive, use =FIND("F",A1). You know how to do the rest... -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com &...

search perticular result of conditional formatting
I have applied conditional format to very large range of data. Many cells are now having a perticular format as a result of these cells meeting the specified condition How can I find those cells because find-format is not doing this task. Harsh, You need to test whether the cell meets the CF conditions. For instance, if a CF condition is >6, then check which cells are >6. No automated function I am afraid. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Harsh" <harshdhandharia@hot...