Free Microsoft Excel 2013 Quick Reference

Charting Non-Contiguous Data Ranges

Hi all. I'm new poster but experienced excel user. I've never messed with the VB angle, nor macro's, but have lots of experience working with the functions and formulas that are provided with excel. That being said, you can expect that this question is probably not going to be easy. (but I could be wrong.)

What I have is an excel workbook to log and analyze my daily readings from my diabetes meter. The 'Data Input' sheet has a row for each day, and each row consists of, (among other things not inportant to this question), five 2 column sections for my readings. They are Breakfast, Lunch, Dinner, Bed Time, and Special (the last one is usually blank. But is there for readings that don't fit the first four)

Each section has two columns, Time (time of the reading) and Reading.

Now.. I have a second sheet, called Trends, where i'm using frequency to count reading ranges. These use Offsets, as new days are inserted into top of the 'Data Input' section (by copy and insert copied), and the frequency is reading only the top 7 or 30 lines. (giving me a rolling weekly and monthly figgures)

I had to use five Offsets each with a single column width, because excel treats the times as numbers which was throwing off my frequencies.

What I would like to do, is create a line chart, that shows all the readings (skipping blanks) for the previous week and/or month, using offsets that check the 5 sections, for previous 7 and/or 30 days.

I have no idea how i'm going to accomplish this. Any help?

I am using Excel 2007, and have experience with programming (C++) so can handle the more technical angle of formulas and functions. A copy of the excel is included. any other suggestions would be welcomed.


Post your answer or comment

comments powered by Disqus
What is the temporary workaround to use time intelligence functions for non-contiguous date ranges?

For example, I'm having the same error that this user is having. For example,
my new measure created is:

=calculate(sum(TE_INTL[Entry Approved Amount]),dateadd(Calendar[Date],-1,month))

However, my datetime table has data in a non-contiguous date range. What would be the work around formula and where would I input it?

http://cwebbbi.wordpress.com/2010/06...2%80%99-error/

Rob Collie mentions a temporary solution to check if a particular date is selected, what is the solution?

Hello,
I am trying to make an XY scatter plot of a rather large non-contiguous data set. I am running into a problem in that the box to enter the X Values and Y Values is not large enough for the entire reference to the cells I want to chart.
They are all on the same sheet just typically (but not always) 7 cells apart.

For example the x values are on the Results sheet in cells B15, B18, B25....etc.
but to reference these I have to use:

=(Results!$B$15,Results!$B$18,Results!$B$25,Results!$B$31,Results!$B$37,Results!$B$44,Results!$B$50, Results!$B$56,Results!$B$63,Results!$B$69,Results!$B$75,Results!$B$82,Results!$B$88,Results!$B$94,Re sults!$B$101,Results!$B$107)

Is there a way to make this reference without having to repeat the " Results!$B " every time. All X data is on the same worksheet and in the same column and all Y data is on the same worksheet and in the same column.

Thanks for any suggestions.

i used this code to Determine a Non-contiguous columns range
column b,c,v

i want last row in all column = a variable = last row

i used this code

	VB:
	
 test() 
    Dim lastrow As Long 
    lastrow = Sheets(2).Range("b" & Rows.Count).End(xlUp).Row 
    Dim myrng As Range 
    Set myrng = Sheets(2).Range("B6:C" & lastrow, "V5:V" & lastrow) 
    myrng.Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but it selects all columns from b to v = 21contiguous columns

i want to select just 3 columns
b,c and v all to lastrow

it is very important to me
thanks

Hey all...

I'm trying to create a combination chart with three data ranges on 2 axis...
the primary data source is a volume category represented on the primary "Y"
axis in hundreds, the secondary data source is a percentage category
represented on the secondary "Y" axis in 0.00%... when using these first 2
data sources everything is fine... I need to add a third data source , also
to be represented as a percentage category consistent with the same scale as
the previous percentage... Excel will allow me to add the third data source,
however, when I do so I no chart the secondary data source, it is replaced
with the thrid data source... how can I see all 3 chart series

--
Thanks for your help -
Joe Mac

Hi everyone,

I'm trying to do a vlookup on a non contiguous named range.

I have a non contiguous named Range called "temp".
I'm trying to perform a vlookup using this range by I'm getting an #N/A error
Any ideas how this works?
Thanks.

Does anyone know how to make a bar chart with multiple data ranges?

For example, you have figures for Actual and Budgeted expenses for 2005 and 2006, and want to project them on the same chart.

One of our accountants was asking me, and I didn't know how either. Please help!

Hey all...

I'm trying to create a combination chart with three data ranges on 2 axis...
the primary data source is a volume category represented on the primary "Y"
axis in hundreds, the secondary data source is a percentage category
represented on the secondary "Y" axis in 0.00%... when using these first 2
data sources everything is fine... I need to add a third data source , also
to be represented as a percentage category consistent with the same scale as
the previous percentage... Excel will allow me to add the third data source,
however, when I do so I no chart the secondary data source, it is replaced
with the thrid data source... how can I see all 3 chart series

--
Thanks for your help -
Joe Mac

Is it possible to chart non-consecutive data. I haven't been successful in
my attempts so far. Data is in same row, ie. row 4; columns needed are H4
and J4 (need to be stacked), next series is K4 , L4 & M4. Unfortunately, I
can not rearrange the cells to make this easier.

Any help or suggestion would be appreciated. I haven't done that much
charting with Excel (2003).

TIA

Hi,

I use a spreadsheet which has non-contiguous data in Col Q. How do I find the last cell in the column (it is a grand total) using Excel VBA 2003?

I specifically want to first make this number appear on a spreadsheet (i.e. pulled out by the VBA code), and then perform a comparison with another number).

Thanks

Hello everyone!
Please excuse me if this topic has been broached before, but here goes...
Basically, I am setting a range that consists of cells that are non-contiguous using Autofiltering and the .SpecialCells(xlCellTypeVisible) property. I would like to use this range as source data for my 'xlXYScatterLines' chart. If I try to set this range as the source data, the macro spits a "Runtime error -1004, Unable to set the Values property of the Series class".
How do I get around this problem?
Thanks in advance, for all the help.
-Nagi.
p.s., here is the code :

With ActiveSheet.AutoFilter.Range
	Set rng0 = .Offset(1, COLUMN_RF - 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            Set rng1 = .Offset(1, 9 + itag).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            rng0.Select
End With
Sheets(chart_name).Select
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count)
	.name = CStr(vcc(ivcc)) + "V," + CStr(temp(itemp)) + "C"
            .Values = rng1
            .XValues = rng0
End With


Hi,

Firstly, I have been using this forum for years as a spectator and would just like to acknowledge how helpful it has been.

I have a spreadsheet that has multiple vertical ranges that may or may not contain data. These ranges have the same row numbers but are in different columns, and they sit above a horizontal range.

I need some code that cycles through each cell in each of these ranges (from top to bottom), and, if a value is found, displays that value in a horizontal range further down the page. So the first value in the left most vertical range becomes the leftmost value in the horizontal range further down the page. Then, if there is another value in the leftmost vertical range (under the one we have already copied and pasted), this is pasted in the next cell to the right of the horizontal range (to the right of the previously pasted value). Alternatively, if there is no value in the second cell down in the leftmost vertical range, then the code starts looking at the top of the next vertical range to the right, and pastes the value found (if found), in the first blank cell in the horizontal range (to the right of the previously pasted value). I need this to rinse and repeat for each of the vertical ranges that sit above the single horizontal range they are populating.

The horizontal range is non-contiguous, with blank cells or 'non-range' cells in between the cells in which I want the values pasted.

I hope this makes some kind of sense, if you find you have read it 16 times and it still doesnt please get back to me and I will try and clarify. Thanks in advance for your help.

I can select non contiguous cells using

with no problem

I can further select to the end of one column data with:

with no problem

however when I try to put these together I using the following:


I get the error:

Run-time error '1004':
Method 'Range' of object '_Global' failed

I searched in vba help for what this may mean but no results found.

Hi, I created a chart with three separate series displaying values across three separate date ranges. Each of the three date ranges for each series reflect the fact the values were taken at intervals of one week and because of this, I set the chart to display an X axis scale with an interval of 7 days between mark.

My chart is using a continuous date range scale to bridge the gap in time between three phases of values and so it's leaving spaces between three separate series of values.
So what I want to know is if there is a way to make the X axis of a chart display a non-continuous date range across the chart. I'd like to keep the scale at intervals of 7 days where there are values in each data series but shorten or remove the gap in time between the three series. Is this possible?

I don't know if this is very clear and I have searched for an answer although I don't know if I'm using the best search terms. I found lots of answers for people having problems with charts displaying different things for cells with no value but this is not my problem.
I'd appreciate any help!
Many thanks,
Dave.

When I select two non-contiguous areas on a spreadsheet - B3:G3 and B14:G14, and create a pie chart, the B3:G3 range (a list of months) does not show up on the axis.

Spreadsheet is attached.

Hi all,

First post as a newbie to VBA and Excel - have found these forums greatly useful in the past and am severely at a loss for how to accomplish the task at hand. Essentially, I want to create a macro that will auto-populate a predefined non-contiguous range of cells with a value that is based on another cell's value. The value that is populated depends on the cell's column number, which corresponds to a user inputted table.

I've attached a sample worksheet (the yellow cells are the predefined non-contiguous range of cells) - I do need a VBA solution, rather than formulas within the cells, because part of my macro will determine a unique placement of the non-contiguous range.

I know I can use a named range to address the non-contiguous range of cells in my macro. What I think might work is to identify the column number for each cell within the named range and insert the corresponding value in the user input table (perhaps using offsets?) I just don't know where to start - any help would be greatly appreciated!

Hi,
I'm in the early stages of learning VBA (much thanks to forums like this !) and I've searched high and low on the web and in my VBA book, but have yet to find a working solution to my problem:

I have a simple bar-chart on a worksheet named "Charts", and the data range it's pointing to is on a worksheet named "Model", all in the same Workbook. The data series is called "Strategy HPR", which I named manually using Select Data on the chart.
I have a macro that allows the user to select a date range on which to run a model, and then populates a range on the "Model" sheet with a column of dates and a column of output values from the model corresponding to each date. Therefore this output range size will vary in length, and I would like the chart to be able to adjust the "Series Values" and the "Horizontal (Category) Axis Labels", just as if I were to manually right-click the chart, Select Data, and adjust the ranges myself.

Here's an example of the data on the "Model" sheet (Columns A & B, rows 22-26):

------A------ B
22 3/17/2007 1%
23 4/21/2007 3%
24 5/19/2007 5%
25 6/16/2007 2%
26 7/21/2007 1%

Here's an example of my code (hopefully I'm doing the code tags correctly):

	VB:
	
 ChartRange1 = Sheets("Model").Range("B22", Range("B22").End(xlDown)) 
Set ChartRange2 = Sheets("Model").Range("A22", Range("A22").End(xlDown)) 
Sheets("Charts").ChartObjects("Chart 3").SeriesCollection("Strategy HPR").Values = ChartRange1 
Sheets("Charts").ChartObjects("Chart 3").SeriesCollection("Strategy HPR").XValues = ChartRange2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get "Run-time error '438': Object doesn't support this property or method" on the SeriesCollection lines above.
Also, the active sheet is "Model" when the macro is executed, which allows for the use of xlDown in the code.

I simply want the macro to have the chart adjust the x-axis labels and data to show only 5 data points if the date range is 5 months (set by the user), and 12 data points if the user chooses 12 months.
Any help would be greatly appreciated !

Hi all,
I have a split named range covering non-contiguous areas, ie:

Name: status_web
Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933

This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: =COUNTIF(status_web, "*p*")

Is there any way to get this to work, or do I need a separate named range for each contiguous section?

NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.

Regards
Rob

Hi all - First Post.

Not sure if this is possible, and suspect not, but looking for efficient solution if its not.

Hope This make sense - It is Hack out of a temp sheet but I want it working before integrating into project

I have a datasheet with 135 data columns and currently 500 Rows of Data.

In some cases I could just use all 135 columns in the .ColumnCount property then reference the relevant column, but here is the guts of what I cant figure out.

1. Can I use, for example, column 5 as the column this is displayed in the ComboBox
2. Can I have Textbox display any of the 135 columns, before or After (Have After working)
3. Can a non Contiguous union range be used to limit the the scope of the

	VB:
	
.ColumnCount=135 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
property, as I assume it would be expensive to have 135 x 500(+) plus data available to ComboBox, particularly as I would be doing it about 20 times.

A great deal of these columns need to be used for filtering (25) and Display (in TextBoxes) to reduce selection hell in the ComboBox for the Project Id.

Using following code I ensure I create dynamic range for each column that can be used by the userForms


	VB:
	
 
Public Sub CreateNamedRanges() 
    Dim iFinalRow As Integer 
    Dim iStartColumn As Integer 
    Dim ifinalColumn As Integer 
     
    iStartColumn = Range("A8").End(xlToRight).Column 'Gaps at Top and Bottom for working
    ifinalColumn = Range("IV8").End(xlToLeft).Column ' In case columns get added
    If iStartColumn > 10 Then ' in case I didnt have a gap, it would go to 135 as istartcolum
        iStartColumn = 1 
    End If 
     
    iFinalRow = Range("p65535").End(xlUp).Row ' find last row of data
     
    Range(Cells(8, iStartColumn), Cells(iFinalRow, ifinalColumn)).CreateNames Top:=True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But...

What I also want to achieve is with a union range, ComboBox and textBoxes display relevant project data in the textboxes based on the selection in the combobox.


	VB:
	
 Range 
Dim rng1, rng2 As Range 
 
Set rng1 = projMaster.Range("projId") 'Column 12 in Data
Set rng2 = projMaster.Range("finType") ' Column 36 in Data
Set rngU1 = Union(rng1, rng2) 
projMaster.Names.Add Name:="Range1", RefersTo:=rngU1 ' Make temp Union workbook Name

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is used by the Following


	VB:
	
 UserForm_Initialize() 
    With ComboBox1 
        .ColumnCount = 1 
        .ColumnWidths = ";0 pt" 
        .RowSource = "projId" 
    End With 
    With ComboBox2 
        .ColumnCount = 2 
        .ColumnWidths = ";0 pt" 
        .RowSource = "projDesc" 
    End With 
    With ComboBox3 
        .ColumnCount = 2 
        .ColumnWidths = ";0 pt" 
        .RowSource = "Range1" 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Which in turn is used by

	VB:
	
 ComboBox3_Change() 
    TextBox10.Text = ComboBox3.List(ComboBox3.ListIndex, 1) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Have searched around widely but cant seem to find specific details needed to achieve without iterating or loading into arrays and using that. Trying to use as much built in functionality as I possible can as I am inefficient coder and I hate recreating wheels .

Thanks in advance

Cheers CB

Hi,

New to the board but hope you can help me. I have a spreadsheet for testing equipment, but for every test I also need to print a single hard copy form for signing and storing. What I would like is a macro that, when a user selects a row on sheet 1, it copies the data from several cells on the row (non contiguous) and pastes the data into specified cells on a sparate sheet in the same work book.

I haven't used VBA for many years and have all but forgotten all I ever learned, but my memory keeps telling me that selection.copy may help me, but I think that would only copy the entire selected row.

Any help you can give would be greatly appreciated.

Steve

Hi folks,
Is there anyway to iout a data range on a chart using a formula?

Here is the deal:
I choose from a drop menu the data for my chart; but i have different number of columns for each option. When I choose some option with fewer columns I dont want to have that "empty" space on my chart.

Is there any way to input a VARIABLE DATA RANGE?

I dont know if I made myself THAT clear... soory all.
Thanks in advance,
Brites.

I have a range (SOURCE) that consists of non-contiguous cells in a column
(eg. ='Log Sheet'!$O$61:$O$110,'Log Sheet'!$O$125:$O$174,'Log
Sheet'!$O$189:$O$238,'Log Sheet'!$O$253:$O$302,'Log Sheet'!$O$317:$O$366,'Log
Sheet'!$O$381:$O$430). When I use the range in a formula
(=COUNTIF(SOURCE,"ABCD") it returns the #VALUE error. What am I doing
wrong?

TIA
Joe

I have created a 2 Y-Axis chart with 12 data ranges. Numerically, the first
8 data ranges belong on the 1st Y Axis, and the last 4 data ranges belong on
the 2nd Y Axis, but MS is choosing to put the first six on the left Y Axis
and the last six on the right Y Axis.

I need for the chart to display 8 on the left Y Axis and 4 on the right.
Please advise.

Thank you

I have a have a chart that is based on data set that is populated form a ms query runing against an Access database. To make this work I need to dynamically change the Data range in the chart to accomodate whatever size is returned in the result set form Access.

From another thread, I leaned of the possibility of using OFFSET in combination with a Dynamic named range as the Data range of my chart. I am however running into a problem with the chart reverting back to the static value returned by the OFFSET formula rather than using the dynamic value every time.

I have my OFFSET formula working with no issues (see below):

=OFFSET('RawData'!$A$1, 0, 0, COUNTA('RawData'!$A:$A), 3)

I also have a dynamic named range in excel using the name "RawDataRange"

When I add that named range to the chart Data range box, the chart displays the correct data.

However, the chart changes the "Data range" value to resolve my dynamic named range, changing from

=RawDataRange

to

='RawData'!$A$1:$C$5

so when I reopen the file, the chart is back to having a static data set instead of my dynamic range. Is there a way to make the Dynamic named range stick and not be resolved to the current value?

Apologies if this is somewhere on the forum already, I may have just not been searching for the right term...

I'm trying to make a control chart using Excel. However, my data is being constantly added to one or two points at a time. I have two questions pertaining to this.
First, is it possible when making a chart to have one cell with a desired Y value apply to a number of X values without having to create a matrix (i.e. have a copy of the cell I want for each of the separate Y values)?
Secondly, is there a way to have the chart data range be variable, as in when I add another point to my list the chart automatically updates for this point? Currently, I can make the range larger than I need but it puts zeroes in for everything that I haven't yet put into the chart.

Thanks for any help!


No luck finding an answer? You could always try Google.