Would like to know if it is possible to create a kind of conditional formatting for graphs. I need to work on a graph and when the value is negative the bar changes of color.
Is there any tool or macro code that can do it?
Lets say I have a block of cells (lets say A1:E5) that all reference the value in the upper left hand cell (A1). Could be a formula by itself or one embedded within a conditional format. I reproduced the formula and formats in the A1:E5 block by using the absolute cell reference $A$1.
I now want to reproduce that entire block (including the referenced cell A1) multiple times on the sheet, but the catch is that each new block must reference the cell in IT'S OWN upper left hand corner. (e.g. a block located at F10:J15 must use as it's reference F10, not A1).
How can I quickly reproduce those blocks? I have done a search and replace to eliminate the $ symbols within the formulas to make them relative, but that doesn't seem to work within conditional formats.
The issue right now is I am trying to apply conditional formats to a sheet I already constructed. I don't want to overight the formulas in the UL corner, so I am trying to copy and paste the conditional formatting only. Can't seem to figure out how.
I have a document that is a question and answer (an audit check sheet). Based on how the questions are answered, I need to have a bar within a graph change colour.
Is this possible ? Im not sure if we can use conditional formating or programming to amend a graph.
Would i need to use conditional formatting or vba/macro ?????
I have attached the document with a more detailed outline.
if its possible to run conditional formating on graphs, I can do this fine on the actual data
i.e. Have a bar chart with a number of bars all the same colour and just wanted to have the bars change to red if they drop below a certain percentage.
only running excel 2000
I run a report that has several blocks of information with blank rows separating them. The blocks change from time to time and across pages so they are not always in the exact same rows.
I'd like a macro that can insert page breaks around a certain number of rows (say 60), but not insert one between two rows that have any content. If the page break would fall between two filled rows I'd want it to go to the nearest blank row instead.
The pages would be about the same size but the blocks of information would not be broken up. That's my idea, anayway. I don't know VB so I can't be sure it's even possible.
Provided this is possible, I'd greatly appreciate the help, as would all the other people in my organization who have to do this on a daily basis.
Is there any way I can use conditioning formatting on datapoints in a graph, so that if certain points meet the criteria they will appear with another symbol?
Suppose I would like to change symbol on the datapoints exceeding the value 200 in this graph: Graph.jpg
Can I do that?
I have a data and did not know how to create a bridge charts.
View 1 Replies View Relatedi have attached a sheet where I have 4 graphs on one sheet. I prefer this to an overlay. I would like to add a 5th graph but it would make all the the graphs too small. Is there a way to make the graph background longer. For instance, extend the length of this so that I can put multiple graphs on it and then just scroll from top to bottom to review them? Also, how do I copy and paste these individual graphs into a newly created graph. copy/paste doesn't work on these?
Boiler.xls‎
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies View RelatedI need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies View RelatedI am building a chart from a pivot table and the data changes weekly therefore my chart changes weekly and I want to have the same colors on my graphs from week to week. I created this code to keep everything the same. Problem is when some of the data is not there for a given week (say no calls from a certain city) a corresponding data set doesn’t get created and the macro won’t work. Is there a way to run if the a data set is not available and to keep the colors the same?
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With..............
I need to update a Variable I called "Croy70" by making up its name from two cells
Left(Range("L" & i), 4) & Right(Range("J" & i), 2)) = i
Where i=2 and L2 is "Croydon" and J2 is "ABC70"
I apologise for this simple question, but I appear to have left my brain at home this morning and all the VBA help files are missing from the computer I'm using!
I can start building.
1. User will first input Medication type in first column. Second Column - Dosage Third Column - Start Date, Fourth Column - End date
There will be multiple entries for the same medication because of dosage change. I can simply get them to insert a new row underneath each medication, and they can then input new dosage, new start/end dates
There will be a cell entry for the form start date, as it will be populated on the main page.
The solution I believe will involve an if statement, with argument being if date is still within range of start/end date, that it will generate text on particular date.
I need to build a formula that looks up a vertical value and a horizontal value and retrieves the value where those two values meet
for example
REGION
Grade ST LOUIS MILWAUKEE SOUTH DAKOTA
C10 30,000 35000 45000
C11 27500 25000 30000
C12 22000 60000 23500
I need the formula to retrieve that value for grade C11 and in milwaukee which is 25,000
I have Sheet 1 with a series of 10 lists. Sheet 2 will display the information on Sheet 1 based on the day. For example, the user will input a start date. Excel should display item 1 in lists 1-10 on day 1 in the respective column. Then item 2 in lists 1-10 on day 2, etc. When it reaches the end of a list it should start at the beginning. Day 1 shold be the start date. Day 2 shold be the next day, etc. I have lists 1-10 all ready to go, but I am having trouble figuring out how to go about populating the other columns.
View 6 Replies View RelatedI am building a form with a list of names and check offs next to them. There will be a list of 150 names and 3 check boxes next to each name. I want to be able to create a running total of each check mark. So I have a name with a check mark that marks off SSN, Drivers Licence, drug test At the bottom I want it to give me the total names that have a SS Card, Total that have Drivers Licence and so on.
View 1 Replies View RelatedI have a workbook with 3 worksheets titled “Numbers”, “Letter” and “NumLtr DB”.
The “Numbers” worksheet is a dynamic listing of numbers in Column A, which also is a Dynamic Named Range using the formula: OFFSET(' Num'!$A$1,0,0, COUNTA('Num'!$A:$A),1)
The “Letters” worksheet is a dynamic listing of letters in Column A which also is a Dynamic Named Range using the formula: OFFSET('Ltr'!$A$1,0,0,COUNTA('Ltr'!$A:$A),1)
I need a macro to duplicate in column A, on the worksheet titled “NumLtr DB”, the first number down as many rows as are needed to equal the full count of letters, and then, in column B, list each letter in succession. This process needs to be repeated once for succeeding each number and then stop.
We have a number of dogs and information about their sire (male parent), dame (female parent) - and those dogs parents.
In essence something like this:
DOG1: DOGDAD, DOGMOM, DOGDAD's DAD, DOGDAD's MOM, DOGMOM's DAD, DOGMOM's MOM
So...I'm thinking 7 fields for each dog.
I started out with an Excel s/ sheet that looked like this:
Dog Sire Dame SireD SireM DameD DameM
DOG1 DOG2 DOG3 DOG4 DOG5 DOG6 DOG7
DOG8 DOG9 DOG10 DOG11 DOG12 DOG13 DOG14
DOG2 DOG15 DOG16 DOG17 DOG18 DOG19 DOG14
Now here comes the issue....what we are trying to do is to find and exclude any dog (in the first 'Dog' column that share ANY grandparent (SireD, SireM, DameD or DameM) with any other dog from the first column.
(As an example above - DOG8 and DOG2 share the same grandparent (DOG14) and needs to be excluded from the final list).
I want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
I am new to pivot tables and charts and to build.... I heard that the best is to use pivot tables but no clue how to do it I have tried but no luck....
How can I see the following information in chart?
-Non con close out time by person- so who is failing to close out on time in this case is completion date
-Frequency of reports by area/ shift
-Number of reports by month, split into critical/major/minor
-Main reasons for reports so trend of type of record
Fairly simple question, but I don't remember syntax.
The simplist cell reference is =F27
If you want to make the column reference a variable and the row reference a variable (=ColumnVarable:RowVariable) What's the syntax?
I want to build a file(name) list of all files in a directory including the ones in all underlying subfolders on any level.
E.g. make at total list of all possible files (not folder-names) in the given directory.
I have a clue, but I just manage to list the files in the first subfolder level - not proceed "deeper" in the folder tree.
In a column, there are 4 option for each cell -
"PAYMENT DUE"
"PAID"
"UNDERPAID BY..."
'OVERPAID BY..."
These are filled in by a formula
I would like to make them 4 different colours but excel only allows 3 options
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?
View 2 Replies View RelatedI seem to have a problem using conditional formatting with Vba in Excel 2003
When I run -
Must admit the R1C1 still confuses me, but somehow I have this working. Currently the RC4 displays in conditional format as $D8 and the RC4 as $C8, but I'm confused at how it knows to start at R8? Is there any way to make the formulas read ="=$D8=""No""" instead?
View 2 Replies View RelatedI have two spreadsheet "book1" with data in it, "book2" that takes all the information from "book1" the cells are linked in "book2" from "book1" so when someone updates "book1" the information is viewable in "book2"
In "book2" where I am getting stuck is as follows,
Column A contains data "break time" that is formatted in HH:MM:SS column B contains a "total duration" in HH:MM:SS. what I need to do is if column A data is = to or more than 4% of column B then I want to turn the cell in column A "red" if it is less then turn it "green". I have tried conditional formatting and it appears to work the once only, I dont no if this is because its 2003 and not 2007??
What I need is to repeat the conditional format over and over again forever is there any code for this I can use?