Vary Data Marker Size By Value?
Apr 17, 2014Is it possible to vary the size of data markers (eg spots) in a chart by their value?
View 2 Replies
ADVERTISEMENT
Changing Marker Size In Charts For All Series At The Same Time?
Apr 24, 2014I have a chart with 5 different series. Instead of editing each one, say the marker size, is there anyway I can change the marker size for all of the different series at the same time?
View 1 Replies View RelatedVary Source Data Via Combo Box
Feb 23, 2007I'm trying to amend a chart via combo box. Cell C15 returns a dynamic named range, based on a vlookup.
My problem is in having the code pick up the value that C15 returns, rather than the formula in it.
Set rng = Sheets("Chart lookup").Range("C15")
ActiveChart.SetSourceData Source:=rng, _
PlotBy:=xlColumns
Excel 2010 :: Add Pie Chart Into Data Point Marker On Line Chart
Nov 27, 2011I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
CopyDown To The Last Row Of Data As This Will Vary From Sheet To Sheet
Nov 9, 2009I've have a piece of coding.
Sub CopyDown()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]=1,R[-1]C+1,R[-1]C)"
Range("A1").Select
ActiveCell.FormulaR1C1 = "20090000"
Range("A2:A200").Select
Selection.FillDown
Range("B2").Select
End Sub
This works fine, but I would like it to look in Column "M" but still insert in Column "A",
Also instead of Range("A2:A200") I would like it to copy down to the last row of data
as this will vary from sheet to sheet.
Bar Chart With Marker
Feb 16, 2007to create a chart which shows a current value in relation to it's annual high and annual low. I've created a bar chart and added a series with a value of 100. The next series I added is the value of the marker I would like to ride along the bar.
My question is how do I change the marker value series from a bar to just a marker? I would like the chart to look like this (except a bar, not a line):
__________
Get Text To The Right - Length Will Vary
Feb 8, 2011If I have a cell with this info: [6126]BOB SMITH
What formula can I use to get BOB SMITH. The length of the name will vary. The number will change, but will always be 4 digits and will have the brackets. [XXXX]
Automate Marker Options In Spreadsheet?
Jul 2, 2014I have a scatter plot in which there are, for example, twenty plotted points representing one of three major categories.
We're using a red square, green triangle and blue circle to represent our categories on the graph. Going in to every plot point and manually changing each point so that it has the proper color and style is very time consuming.
Is there a way to designate in the spreadsheet (in its own column or something) that I always want the plot point to be represented in a certain way (like a red square) when graphed?
How Do I Modify The Comment Marker/symbol
Nov 15, 2008I would like to insert images (like a questionmark symbol) into a spreadsheet I'm working on, and then when the user hovers the mouse over the questionmark a "help-text" pops up. I've noticed that it doesn't seem to be that simple making a tooltip, however, is it possible to replace the small red triangle that is inserted into the sheet to indicate a comment with an image?
View 14 Replies View RelatedImage As Custom Xy Chart Marker
Oct 19, 2007I'm trying to use an image as a custom marker on an XY scatter chart. The jpg image is embedded on a separate worksheet and when I copy and paste it via code it seems to be arbitrarily resized. I've also tried to resize the embedded image and set the marker size but it always appears about 3 times the size of the original image. See CASE -0.5 below.
Set seriespoint = chtBaseArea.SeriesCollection(" Events").Points
For i = 1 To rngCombPointsY.Rows.Count
With seriespoint(i)
.HasDataLabel = True
.ApplyDataLabels Type:=xlDataLabelsShowLabel
.DataLabel.Text = rngCombPoints. Cells(i, 1).Value
.DataLabel.Position = xlLabelPositionAbove
.DataLabel.Font.Bold = True
.DataLabel.Font.Size = 8
Select Case rngVehicleY.Cells(i, 1).Value
Case -0.5
Set shpTemp = wrkshtGraphics.Shapes("Soyuz_Small_Icon")
shpTemp.Copy
.Paste
.MarkerSize = 10
Vary Columns Based On A Number
Feb 5, 2009You can always add numbers together. however, how can you vary columns based on a number?
for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.
Adding A Coloured Marker On Chart Y Axis.
Aug 7, 2009I have a simple Excel 2007 column chart which has names listed along the y axis for each column. Associated with each name in a small table is a colour reference. Is it possible to add a small marker (say a triangle or something) that aligns with each column?
Or possibly (or in addition) colour each column differently and in accordance with the assigned colours? I'm doing this with some vba code. but if someone can point out the general principle I should be able to work that in.
Hiding The Active Cell Marker / Placeholder
May 24, 2006I feel a bit silly asking this in the presence of so many obvious excel gurus: but here it goes. I've created some user forms, activex controls, etc. that do a series of complex calculations, supported by VBA and it's at the point where I want to roll it out. How do I hide / get rid of the active cell marker on the sheets? I've looked in the "options - view" section and have lucked out.
View 2 Replies View RelatedTesting For Values That Vary Within Specific Tolerance?
Apr 22, 2014I'm trying to identify where a set of values 'agree' within a specified tolerance.
I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.
To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.
I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.
Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.
How To Make Universal List That Can Vary By Different Location
Mar 12, 2014I am trying to use HLOOKUP to find a location in another sheet and display all the names in that specific location. Also can change location at will and will display the names for that location.
I have attached a spreadsheet that kinda has what I want. It does not have to be HLOOKUP but that is all I know, Hlookup and vlookup.
Opening File Paths Where Filename Will Vary
Feb 4, 2014I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.
Is it possible to write something like:
Code:
Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"
Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?
Range - Dynamic Use #rows & Cols Vary
May 29, 2007i have data that will vary from item to item in the $ of rows associated
and the # of columns to be dealt with.
item1 Line 1
Line 2
Here i want to have a formula put and copied across X columns
item2 Line 1
Line 2 other data going across in the columns
Line 3
Line 4
Here i want to have a formula put and copied across X columns
item3 Line 1
item4 Line 1
get my meaning??
columns involved will be known before i get to this bit of code !!
as an example lets say it's 8 columns
using the record macro i get this bit of code
Range("E14:M14").Select
Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
This is fine but i don't know what row or the columns are before i run the macro so the Range
("e14:M14").select wont really work.
I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns
this is close to my idea in my mind conceptually
Range("C2:C" & LastRow)
but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.
Graphing - Macro Won't Change Marker Fill Colour
Jan 14, 2014I'm recording a macro, so a line in my graph is a specific green. The marker fill won't retain the green, it always ends up blue. the lines and marker line is green. no matter what color I try the marker fill will always be blue. How to correct this?
View 6 Replies View RelatedSetting Chart Marker Based On Cell Format
Apr 12, 2006I have a dynamic chart which I would like to show markers in a SeriesCollection anywhere the backcolor of any of the corresponding source cells is ColorIndex = 36; otherwise no marker is shown. Something along the lines of:
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred
when copy/pasting, but IS if the keyword uses "quotes".
With Sheets("Chart").ChartObjects(1).Chart.SeriesCollection(1)
For i = 1 To UBound(.Values)
If "something here to check if .Interior.ColorIndex = 36" Then
.Points(i).MarkerStyle = xlMarkerStyleCircle
Else
.Points(i).MarkerStyle = xlMarkerStyleNone
End If
Next i
End With
Excel 2010 :: Creating A Simple Line Marker Graph?
Mar 2, 2014I'd like to create a simple graph exactly like the one on the picture, but for the life of mehow to do this on Excel 2010.This is the data I have:
Seperation First target Second target
0 59.00% 0.00%
2 64.28% 40.62%
4 62.68% 48.99%
6 60.81% 65.83%
8 61.82% 70.21%
Format A Range Of Cells, Columns And Rows Will Vary In Length
Jun 16, 2009I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
Charts: Vary Colors By Point With Area Fill Effect
Oct 30, 2006I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series
So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?
Excel VBA - Change Marker Styles For All Series Excluding Unmarked Lines
Aug 4, 2014I have a macro that changes the marker style for all series in a scatter chart, chosen through an input box.
[Code].....
It works nicely, but I want it to exclude any series that do not have markers (i.e. lines used to indicate a reference value). I've tried an if-then statement after the With statement, but it will add markers to any line series:
[Code] ......
Basically, if the series has markers or line+markers, I want it to change the marker style, otherwise do nothing with the series.
Excel 2007 :: Macro - Copying Column In Which Number Of Rows Can Vary
Sep 23, 2013I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.
The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?
Code:
Sub IPT()
'
' IPT Macro
'
'
Range("E1").Select
[Code] .....
Detecting Size Of Data On Clipboard
Mar 22, 2010I am trying to write a macro to paste the data that has been copied onto the clipboard into my worksheet. However I want to paste it in different ways depending on what data is there.
This is what I have so far:
Sub ImportTissue()
Sheets("Tissue").Select
If "dimensions of clipboard data are 5 cells by 5 cells" Then
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
[Code] .........
Sorting Data With Dynamic Size Using VBA
Jun 8, 2014I currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data (I can't used the named ranges as it should exclude the first row which is the header), is there a way I can make it dynamic too??
** The Europe table is from A1:G5 where Row 1 is the Header and Row 5 is the Total
Code:
With ThisWorkbook.Worksheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add Key:= _
Range("G2:G4"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
.SortFields.Add Key:= _
Range("F2:F4"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
[Code] ........
Auto Size Cells Depending On Data In Them?
May 30, 2014I am trying to auto size cells depending on the lenght of the data in them. I have added some code in the work book on the attached file, this works on cell that i maunally add data to any where on the active page (template) but the cells that are auto updated with currencey data dont expand?
If you double click column H a list appears select a test and this auto populates the charges accociated with it
Is there a way to do this as it just fills with #### ???
The code in this workbook is below
[code]
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim actCol, curCol
[Code]....
Font Size In Data Validation Dropdown
Dec 11, 2013Do we have control over the font size in a drop down list created using data validation?
Mine looks like this and I would like to make it more readable by increasing the font size.
data.png
How To Write Data Of Different Size From Matlab To Excel
May 29, 2013I have got many files lets say 30 files ,in each file there are 3 columns of data but the no: of rows varies for each file .for eg 1 file has got 6 rows and 3 columns then next file has 3 rows and 3 columns etc .i was not able to append it since its size varies.i need 2 write all these data in an excel sheet also . As u can from the excel sheet for clearance which i have done manually for a single speaker.like dis i have got many speakers so i need 2 code it
View 3 Replies View RelatedCalculating Average From Range Of Data That Changes Size?
Mar 24, 2014I'd like to work out the average of a set of data which changes length and position. It's probably easier if I attach a workbook.
The different sets of data are split by blank cells so they can be distinguished.