I would like to shade the rows with alternating colors so that all the data pertaining to a given ID can be easily spotted. In the example above, I would need A2:C4 to be shaded one color, A5:C5 another color, and A6:C8 the first color again.
I have a column with (for example) 5 entries A1, A2, A3, and so on. I would like to pick up this data, tranpose it into a row, put it on the 2nd sheet, but (again for example) be specific about where i want it to go. I may want to have A1 at B2, A2 at E2, A3 at H2, etc
I have A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.
If I were to do it manually 365 times, the macro would look like this:
I have a spreadsheet where one column will determine the color of the entire row....if the sales persons name in column B = "Dew", the entire row should be shaded light blue. if the name is "Swiger" they should be white, when its Webb, it should be light blue again- alternatively, at every change in value in that column, alternate the entire rows shade - when the sales person name changes from Dew to Swiger, change the shading of Swigers Rows to white, when it changes from Swiger to Webb, make Webb's rows shaded the same blue as Dew's row's were....there are multiple rows for each sales person.
My question is a display/ chart format question. I need to convey what I am showing on this chart in a less "busy" fashion. Is there a way to display the data in an easier to read and see format? In particular, is having both averages on the chart in the same format an issue?
I 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.
I setup a table with a column titled "Type". Each value under column "Type" defines the type of row it is: "Section", "SubSection", "SubSubSection"
I wish to create a set of conditional formatting rules that apply to data rows in this table, each rule controlling how cells within a whole row should be formatted, according to the value found in "Type", for that row.
Example:
Rule1: [@Type]="Section" -> fat red line on top of cell. Rule2: [@Type]="SubSection" -> thin black line on top of cell. Rule3: [@Type]="SubSubSection" -> dashed black line on top of cell.
I can't seem to make this work.
How can I leverage the nice column names, and the "@"-this row designator, within a table to create conditional formatting rules that apply to all the cells within a row, in the same way one can refer to in table formulas?
I have a need to expand data rows such as the following example:
A cell in a column of alphanumeric characters contains for example: 9,2,36J,10-13,42L-42N In this example I need to replace this row with 10 new rows (deleting the orginal) so they would be expressed like this: 9 2 36J 10 11 12 13 42L 42M 42N
The master sheet contains company information; each row represents a (company)record.
The first column contains the country code where the company resides, e.g. DE.
I dug up this piece of For i = 12 To 1 Step -1 Set rngAutoFilter = Cells(1, i) rngAutoFilter.AutoFilter Field:=1, Criteria1:="DE" Range(rngAutoFilter, rngAutoFilter.End(xlDown)).SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Sheet2").Cells(1, i) rngAutoFilter.AutoFilter Next i
I found this code to work allright but, it only copies the singel cells containing "DE" and the topfirst row. At least that is what is the visible result.
How do I modify this code to copy EACH complete row containing "DE" in the first column?
I would like to know how can i insert a blank row in between ACC# when ever a new series of data begins
name Acc # Balance Date John 2222 200 june 2013 John 2222 344 june 2013 John 2222 700 Sept 2013 Silva 4444 333 June 2013 Silva 4444 333 June 2013 Silva 4444 333 June 2013 Raff 5555 897 June 2015 Raff 5555 897 June 2015 Raff 5555 897 June 2015 Raff 5555 897 June 2015
regards the two attached files (both are identical) except '1' has TWO Rows of data above the Macro Button and works correctly whereas '2' has only ONE Row (the desired option but does not work correctly!( Inserts Row in wrong place and incorrectly fill series)).
basically the program inserts a additional row below the last data entered, copies a formula cell from above and finally (the problem area) Series Fills a cell from data above.
I've been trying to work out a code for a while now to plot some series in rows on a graph. The wonderful Mr Pope helped me with one before but for series in columns but i just don't seem to understand it enough to use it for this problem. the graph already has 2 series plotted (called the envelope) so i have to use seperate series to plot the data.
Basically the name of series is down column A but the range is from E1 to Y1 and the data to be plotted on the graph which already has two series plotted in it is E2 to Y2, E3 to Y3 etc. iv been trying to use this code but failing miserably
Sub adddatatopsd(Name As Range, XData As Range, YData As Range) With ActiveSheet.ChartObjects(1).Chart With .SeriesCollection.NewSeries .ChartType = xlColumnClustered .XValues = XData .Values = YData .Name = Name .ChartType = xlXYScatterSmooth End With End With End Sub...........................
I have a chart in a workbook that works fine by looking at the following data series:
=Workings!$A$2:$C$35
In the data series above...where the numbers are 2 and 35 I have named ranges "CHART_Start_Row" and "CHART_End_Row" which will change dynamically as appropriate and can replace these static numbers - but how do I incorporate these named ranges into the data series reference?
I am working on a spreadsheet and I need values from Sheet1!a2, a7, a12, a17 etc...
I want to fill in the formula ='Sheet1'!a2 etc, to fill column A in sheet2!
When I highlight and drop down to fill excel fills the column in increments of five like i need but doesn't start on the right one. Instead it fills the formula as ='Sheet1'!a6, (For cel A1) and ='Sheet1'!a11, (for cell A2) and so on.
am looking to find the most recent value in a series of cells that I define. I have set up a worksheet for each month and I want to only pull out the latest total entered for a particular cell reference (cell B16 in each worksheet has the number I need).
I tried using : =LOOKUP(9.99999999E+307,Jul:Jun!B16) but that just gives me a #value error.
I had a search through the archives too and found [url] and [url](neither of which I really understood or could use, although I did have a go), anyone got any ideas? Am sure it's very easy!
Just to clarify, I have 12 worksheets, each with a different month. Each month I enter data into the relevant worksheet. I want a running total on a summary worksheet and need to pull out the latest numbers automatically (they don't need to SUM or anything).
I am trying to use the auto fill series but I need the series to start over every time the value change in another column.
If i use the auto fill series, it will copy 1,2,3,4,5 and so forth til the end of the column. In this case, i need the auto fill or some other function that will restart once it encounters a different value under the ID column. I've started below to show an example.
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
Sub Macro5() ActiveSheet.ChartObjects("Chart 243").Activate ActiveSheet.ChartObjects("Chart 243").Activate ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(2).Delete End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
LABEL COUNT 1 of 2 empty cell 1 of 2 empty cell 1 of 2 empty cell 1 of 2 empty cell 1 of 2 empty cell 1 of 16 15 empty cells
I need to fill the empty cell with the "series" (2 of 2, 2 of 16, 3 of 16). This is just an example of data - there are all different series - 1 of 12, 1 of 5, etc.
Anyway - was hoping to highlight the whole column and be able to auto fill instead of having to extend each series. I have tried straightforward fill series which doesnt work of course. I have tried go to special - blanks but cant find right combo to make it work.
Will it work or do I have to either do manually or with a macro?
Right there data in cell A1 lets say LTP BY001 and then the next data in column A is LTP BY002 in cell A9. What I need to do is copy LTP BY001 from cell a2-A8 and then LTP BY002 From A10 until LTP BY003 appears lets say A15. Therefore need LTP BY002 copying from A11-A14. I need that to go right the way down the workbook at everytime their is a number change.
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range Dim CellString As String 'Stores a cell range in the form "AA27:AB39" Dim CurrentSeries As Integer Dim SeasonCount As Integer
I have a list of data on one sheet and a 'reports' page on another sheet. The reports page has several pivot tables and a pivot chart. I want the pivot chart to format the bars on the chart relevant to the series name. The series names are "R" "A" amd "G" for Red Amber and Green respectively, I want the chart to change the colour of the series so that it is the correct colour ie. "R" would be coloured Red, "A" would be Amber and "G" would be green.
Sub PivotLoader() Dim Red As Integer Dim Amber As Integer Dim Green As Integer Red = Range("H9").Value Amber = Range("H10").Value Green = Range("H11").Value Range("B8").Select ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh Range("D25").Select ActiveWindow.SmallScroll Down:=18 Range("B49").Select...........................
i need to write a formula that spits out the average of a series of numbers when (if):
every time "CAD" appears (column A) it takes the numerical figure of the cell directly to the right of it (column B). the data is arrange vertically, e.g., the range of "CAD"s is all vertical (column A), about 200 entries.
is there a formula/function that takes data from the columns completely adjacent to one another?
Using Excel 2010. I have a Column Chart that shows 3 data series by month for one year
How can I customize the chart to group the data series on the X-Axis and repeat the 12 month series 3 times on one chart with one X-Axis to show each data series as a group?
