References Within Graph Formatting?
Mar 13, 2012Is it possible to title a graph according to a changing column name perhaps?
View 5 Replies
ADVERTISEMENT
Passing Cell References From A Cell's Text To LINEST Function And A Graph
Mar 2, 2009I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
Conditional Formatting With References
Feb 8, 2014I have 2 sheets. Sheet1 looks like this:
B
D
A
[Code]....
Note: The "X"s are actually not in the cells, they are for illustration purposes only. Instead of the "X"s, I want to fill the cells marked with X with the color red. The cells in sheet2 have no value except row 1 and column 1.
I am looking for the formula to fill into the conditonal formatting field for sheet2.
The formula should detect how many letters there are in row 1 of sheet2 (for example here we have 4, namely A, B, C and D). If I add a new column E later, I just want to re-apply the formula to those new cells, not edit the formula itself. If there is a new column E, it will also be added on sheet1, but not necessaryly to the right side of the column with the header "A", maybe it will be added between "B" and "D".
The number of rows will not change, they stay static at 1-5
Here is the underlying task.
Sheet2: A column that has all days of the year, from Jan 01 to Dec 31, from top to bottom, starting in A2 going down to a366. In column B, C, D and so on, in row 1, I write the names of different countries. Sheet1: In row 1 I write the same country names as in sheet2, but not in the same order. Below the country names I list the official pulic holidays of the whole year.
I need the formula for conditional formatting that will color the intersecting cells of Country names and days of the year. And I need the formuly to be dynamic so that I can add more countries later without have to adapt the formula for new columns manually.
So far I have the formula to count the filled cells in row 1 on sheet1. I also have the formula to find the country name in sheet2 and return the column number of the same country in sheet1. Last but not least, I can make this work with a static formula, but replacing the static pieces with the dynamic pieces mentioned above just won't format the cells as desired.
Conditional Formatting With Multiple References
Jul 18, 2014I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.
What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.
My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.
Conditional Formatting With R1C1 References
Aug 19, 2007Does anyone know if it's possible to use R1C1 style references in conditional formatting formulas?
Eg., =R[-1]C > 1
I tried the above and I keep getting an error.
Conditional Formatting That References Two Separate Conditions
Aug 15, 2008I am trying to use conditional formatting in Excel 2003 to indicate when certain tasks are overdue (by highlighting the cell in red). My problem is that I have multiple criteria. If my line item is a "priority" item, it is considered aged if not resolved after 2 days; if the line item is "routine", the item is not considered aged until after two weeks (14 days). My goal is:
1) I would like the "status" cell in column D (which says "open" or "closed") to turn red if the item is aged.
2) Aging depends on the "priority" status in column A .......
Formatting Graph Won't Work?
Jan 24, 2012I want to change to format on the axes from % to numbers using the code below but nothing happens?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range = "Listbox4" And Range("U4") = 2 Or Range("U4") = 5 Then
ActiveSheet.ChartObjects("Chart 5").Activate
[Code]....
Formatting Line Graph
Feb 15, 2007how to have different format on a line grapgh. I have a series of data points for sales figures. The first 10 are actual data and the next 5 are projections. I want to have a line graph with solid color format for the actuals and a dashed format for the projections.
View 3 Replies View RelatedPasting Data From One Sheet To Another With Formatting And Cell References
Feb 11, 2010I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.
One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.
I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.
Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.
A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.
By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.
Color Scale Conditional Formatting With Relative References
Oct 7, 2013I'm having trouble with Color Scales within Conditional Formatting. I have a data set of commodity prices. In column A I have the name of the commodity, in column B I have the standard deviation of the price change of the commodity, and in Columns C-N I have the monthly % change in the commodity price. I want to conditionally format with Color Scales each row of price changes within Columns C-N based on each commodity's standard deviation (column B). If the price change is a one standard deviation or more decrease, I want the cell to be dark red; if the price change is less than a one standard deviation decrease, I want the cell to be a gradient of light red; if the price change is a one standard deviation or more increase, I want the cell to be dark blue; if the standard deviation is less than a one standard deviation decrease, I want the cell to be a gradient of light blue; and if the price change is 0, then I want the cell to be white.
I can achieve this perfectly by manually doing 3-Color Scale Conditional Formatting for each row, but it's very time-consuming. And Excel doesn't allow me to enter relative cell references when I'm doing the Color Scale Formatting. Is there a quick way that I can do this so that each row is color formatted differently?
I've attached an example file (there are many more rows within the original file) and formatted the first several rows manually as I want the final product to look.
example.xlsx
Conditionally Formatting Bars In Bar Graph?
Oct 21, 2011I am building a graph and i want to conditionally format it so that if a bar is less than 24 it wil be green and if it is greater than 24 it will be red. how to do this?
View 1 Replies View RelatedConditional Formatting For Series On Graph
Oct 23, 2007I have a table which provides the source data for a Bar Chart style graph. I need the colour of the different Bars to change depending on the values in the table. For example. If the value is over 1% then the bar will change to the colour Red otherwise it will stay Green.
View 3 Replies View RelatedCopy Non Absolute Cell References In Conditional Formatting Formula?
Dec 3, 2012I have this fairly simple formula which decides whether to shade a cell or not
=AND($X$1<>"TBD",R3<>"None",AC3="Y")
This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.
Changing Hard Coded Column References To Variable References
Oct 22, 2009I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
Converting Absolute References To Relative References In Formulas?
Feb 10, 2014I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
Structured References And With Cell References I Get A Column Of Zeros
Nov 25, 2007It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
Change Static References With Lookup References
Mar 2, 2009I am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
Make A Scatter Graph That Will Graph The Attached?
Mar 21, 2014I would like to make a scatter graph that will graph the attached. The score would be on the Y axis and the birth date would be on the X axis. This is simple to do by itself but what I would like to do in addition to this is to have the top 25% of the scores a single color, the middle 50% of the scores a second color and then the bottom 25% of the scores to be a third color. And if it is possible to have the ID visible when you move your cursor over a given dot in the graph. Currently when I make a scatter graph the X,Y coordinates show when I hoover the cursor over a dot.
View 7 Replies View RelatedMake A Graph That Will Graph Against Time
Jun 11, 2006How can i make a graph that will graph against time? lets say i have a bank balance like this:
1/2/2000 $500
1/3/2000 $600
3/12/2000 $400
there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.
Line Graph: (line Graph To Display An Amount Over Time)
Dec 30, 2008I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.
A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:
http://img179.imageshack.us/my.php?image=orangeshm4.jpg
Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):
Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).
How To Nest References
Jul 31, 2009What I'm wanting to do is essentially use a reference to update yet another reference.
In more detail, I want to retrieve a value on a worksheet and use that value to alter yet another reference to another worksheet.
For example, I want to retrieve a value (let say its 16 from either a local cell or a cell on another worksheet) and then retrieve yet another value from another worksheet based upon that value (lets say the worksheet I would want is then "Subject 16").
I've attempted various nesting formats of the typical reference formula but obviously have had no luck. Would I need to use VBA or is there a simple solution that I am missing? If I do have to use VBA how should I go about doing this?
Check And Set References With VBA
Mar 11, 2009for example, is it possible to check if a Reference to Set a reference to Microsoft Internet Controls (shdocvw.dll) has been enabled in user Reference settings. And if not set, then set it via VBA
View 4 Replies View RelatedCan You Use INDIRECT In 3-D References
Jul 13, 2006For example
=SUM(INDIRECT(C10))
where C10 would contain
="Sheet2:"&"Sheet3!"&"A"&ROW()
always returns #REF!.
However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.
Circular References
Aug 25, 200815.2
13.9
6.1
9.19
and i wanted to divide these all by 4.
I can do this by clicking on a cell OTHER than one of those four above, and do:
=A1/4 (if 15.2 was A1) and copy that formula down.
But then I have the originals AND the new ones in separate columns.
I want to just be able to divide those original numbers by 4 without resorting to using other cells, but if I do =A1/4 on A1 itself it says that i have made a "circular reference"
SAP Connection - Which References
Sep 7, 2006I'm trying to connect to SAP R/3, and I think I'm not sure which references (Tools --> References) I'm supposed have checked.
All I want to do for now is the following:
Dim sapConn As Object
Set sapConn = CreateObject("SAP. Functions")
Unfortunately, I am getting a "Run-time error '-2147024770 (8007007e)': Automation error. The specified module could not be found."
I have read somewhere random on the internet that I should add "SAP:Remote Function Call: COM Support 1.0 Type Library" which links to the SAPGui Provided library librfc32.dll. However, this alone doesn't seem to work...
Conditional Formatting - Two Different Fields Affect One Cells Formatting For Date?
Sep 16, 2013A 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
Warning Of Invalid References?
Jul 28, 2014Running a worksheet with vba, at the start of the code I turn warnings off and at the very end of the code I turn them back on.When the routines complete a warning is raised:
A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell reference.
When I run Error Checking everything comes up clean.
Clicking on "Show Formulas" shows no formulas for there are no formulas on the sheet to show, just a chart, and clicking "Show Formulas" just turns my dates into serial numbers and screws up the display of the two comboboxes, messing the fonts and drop-down arrows, and since closing and reopening didn't revert the comboboxes back to normal I will now have to blow them away and resurrect them. I also see Show Formulas also messed up my command buttons. nice...
The only way I find to avoid the error is to turn Warnings OFF at the start of the charting routine and NEVER turn it back on.
I made certain that none of my names had any errors in them. Everything looks proper. So what next?
I fear something will create an error for me now along the lines of Murphy's Law
External References That Should Be Internal
Mar 1, 2007I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).
In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.
BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.
I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.
Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.
One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?
Flexible Cell References
Oct 9, 2008I want to do a search for the amount of people of a certain age in a column, but I want to be able to vary the amount of cells I look in. So first I might want to look for people aged 15 in A3:A35 and then in A3:A55 to see if there is a difference.
Now the optimal way to do this, in my opinion, would be to have a reference that looks like A3:A(B1) and then have the number of the last cell I want to look in in B1, in this case either 35 or 55.
References By Using Paths To Other Workbooks
Aug 11, 2009is it possible to use references and concatenate them together that they are still references?
i have in:
file1.xls
sheetX:
A1: 'C:Infonic[file2.xls]sheet2'
file2.xls
sheetY:
A1: test
what i need is in sheet2 of file1
the value of file2.xls!sheetY!A1 = test
the problem is that i need to use the path to file2!sheet2 of the cell A1 in sheetX
is it possible to use sheetXA1 together with sheetYA1 as reference in sheet 2 so that i get back test as value.