Create Chart Using Dynamic Named Ranges For Series
Jul 27, 2012
I've created my dynamic named ranges using the OFFSET function, ex.
="OFFSET(SAMPLE!$D$4,1,0,COUNTA(SAMPLE!$D:$D)-1,1)"
I'm now trying to get my chart to use that range. I read at [URL] .... that I need to make sure my series reference is a fully qualified reference. So I've entered that series reference using the name of the workbook followed by the range name (=2012-PIRS.xlsx!SAM_CLAR2DEL). I have two copies of my workbook (one as xlsm with VBA project, and one with xlsx without VBA) and I can't get Excel to accept the series value in either workbook.
ERROR MSG:
The formula you typed contains an error. Try one of the following:
- Make sure you've included all parentheses and required arguments.
- To use a function, click Insert Function on the Formulas tab (in the Function Library group).
- If you include a reference to another sheet or workbook, verify that the reference is correct.
- If you are not trying to enter a formula, avoid.........
View 1 Replies
ADVERTISEMENT
Nov 30, 2006
I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).
Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:
=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)
When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.
View 2 Replies
View Related
Mar 13, 2013
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?
View 2 Replies
View Related
Oct 13, 2007
I am employing code to label dynamic ranges that takes the form of,
ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))"
'dms_j
ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)"
'dms_p
ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)"
'dms_r
ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)"
'dms_t
ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)"
The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
View 9 Replies
View Related
Nov 11, 2012
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
View 1 Replies
View Related
Dec 25, 2008
I use the standard OFFSET-COUNT-MATCH method to create dynamic named ranges in my Excel projects. Needless to say, this method won't work on a spreadsheet with formulas extending beyond the current range. The count function counts the cells containing formulas, even though they may contain no data. Does anyone know how to construct a formula that will IGNORE the "formula only" cells??
View 3 Replies
View Related
Jun 21, 2009
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies
View Related
Jun 6, 2012
I am looking for a quick way to create a number charts using data from multiple sheets.
I have 12 sheets Apr - Mar that contain data in identical format. I also have sheets 12 sheets Apr Charts - Mar Charts, I need to create a number of charts on each sheet for the relevant month. To avoid having to create a ridiculous number of charts, I thought I would try to dynamically change the chart data series based on a value in say cell A1.
For example if cell A1 of the Apr Charts sheet = Apr, the chart will show the data for Apr.
The idea is that I can copy the Apr Charts sheet the required number of times and replace the value in A1, and my charts will update dynamically. I need to have seperate sheets for each of the monthly charts.
View 1 Replies
View Related
Oct 3, 2007
I would like to update a chart after I have updated the underlying data just by clicking a button and displaying the updated date in the chart. Does anyone know how to do this? Currently I am having to manually click on the graphs line in the chart, which highlights the columns data, then manually extend this for the chart to update. And then I am manually typing in a date cell in the chart. There MUST be a cool way to do this.
View 6 Replies
View Related
Oct 25, 2007
I have a chart with Data Series being dynamic ranges. There is only one series and x & y each have a dynamic range. I need to be able to include Series Name as a dynamic range.
For example. I have 3 rows of values with 3 columns.
Col 1 Col 2 Col 3
Name X-Val Y-Val
A 1 3
B 4 6
C 8 0
I now need a chart that has 1 series with a x range of col2 and a y range of column 3 but a corresponding name equal to Col 1. So when I move my mouse over each point I get the series name. E.G. I hover over poing 1,3 I show the name as A. Or I hover over point 8,0 I get C.
If it isnt possible using the ranges the way I am, then I would be looking for a possible solution. I am sure I could do this by some sort of loop but not quite sure given the number of rows constantly changes.
View 7 Replies
View Related
Feb 9, 2008
I am trying to assign a named range to a series on a preexisting chart in a sheet, but I get the error that "unable to set values property of series class". This is the code example that DOESN'T work:
Sub assign_values()
Dim myrange As range
Set myrange = range("a1")
Set myrange = union(range("a5"),myrange)
activechart.seriescollection(1).values = myrange
End Sub
However, this code DOES work:
Sub assign_values()
activechart.seriescollection(1).values = range("a1")
End Sub
I don't see why the first wouldn't work, but the second would work. I suspect it is something to do with the union function. i am trying to go through a set of data and then group certain cells into a named range to be used on the graph.
View 3 Replies
View Related
Aug 20, 2009
Dynamic named ranges - lots of them! ...
View 9 Replies
View Related
Mar 10, 2014
I have a log where I'm recording daily readings of a pipette. Each day three readings are taken and I have each stored as a dynamic range so that I can monitor how in control my process is. However, when I graph it, I would like it to be one continuous line graph (Three readings from day one followed by three readings from day two, etc.). I'm having difficulty doing any sort of combination.
View 7 Replies
View Related
Jan 17, 2014
I have created a dynamic chart for multiple series. I have created this using the offset function and have named 20 ranges. The problem I have is that if only 19 of the 20 named ranges are populated it reads that the worksheet contains one or more invalid references.
Is there a way to stop this from happening?
View 1 Replies
View Related
Nov 26, 2006
Using the Dynamic Named Range tip, I defined named variables as follows:
Xrange=OFFSET($A$2,0,0,COUNT($A:$A),1)
Yrange=OFFSET($B$2,0,0,COUNT($B:$B),1)
then tried to define my Chart series as follows:
=SERIES($B$1,Xrange,Yrange,1)
but it gives an invalid reference error. What am I doing wrong?
View 2 Replies
View Related
May 6, 2014
I have 153 rows of data to name, with a different offset formula per row.
View 8 Replies
View Related
Nov 24, 2008
I have a question regarding dynamic validation. I have attached a sample spreadsheet to clarify if my explanation doesn't. I have a list of codes that map to a list of other codes that are not one to one mapping. I would like for the other users of this code to populate what the correct code on our side is but without having to go thru the entire list in a validation field. (if that makes since)
Using validation before in a similar instance i used indirect to grab a named range to help shorten the list for them. However in this case that would be a ton of ranges i would have to set up! I've tried Match, countif and index in an offset formula in the validation list but can never get it to work!
View 2 Replies
View Related
Mar 27, 2008
I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.
Can anybody provide a non-volatile alternative to this?
=OFFSET(Query!$A$14,,,Height-13,19)
With Height being:
=MATCH(BigText,Query!$B:$B)
View 9 Replies
View Related
Apr 28, 2008
I have a spreadsheet with a large number of graphs on and lots of data.
Most the graphs look across 20 or so columns and one or 2 rows down.
The problem is that l need to insert new colums in now, and obviously excel graphs dont recognise the inserted column data.
Therefore l believe using named ranges may solve this problem...
I have the formula =OFFSET(Sheet1!$A$1,0,0,2,MATCH("DC",Sheet1!$1:$1))
However i need to expand on this to achieve what i need to do.
1. Firstly l only want to look from column C onwards (so ignoring all data in column A and B)
2. I need the match to match to columns starting with the letters "DC", however there is a number after this, so it is not a direct match. The match needs to ignore columns with any other heading start.
3. is there anyway to automatically refresh the graphs to the named range once it changes, or any simble vb macro i can run?
View 9 Replies
View Related
Feb 3, 2010
I have a dynamic named rage "AREA" which can contains values like "Area1, Area2, Area3, ..."
For each item in list AREA I have another dynamic named range, named exactly as the item, so for example I have a dynamic named range "AREA1" with values "SubArea1.1, SubArea1.2, ..." and also a dynamic named range "AREA2" with values "SubArea2.1, SubArea2.2, ..." and so on, I think it's pretty clear and straight forward (I am modelling the fact to have several areas and each of them has several sub areas).
Now, I have cell A1 being validated against the list AREA, that is, the source for A1 is "=AREA", so that I can choose one of the values in the named range AREA; I also have cell A2 being validated against the list that I choose in cell A1 using the INDIRECT function, that is, the source for A2 is "=INDIRECT(A1)".
I have already realised that this approach for cell A2 does not work, as it seems that INDIRECT does not work for dynamica named cells.
An option would be using the following for the source of A2: ....
View 9 Replies
View Related
Mar 19, 2008
I am trying to create a dynamic named range with a sales rotation tracker. Basically, I have a list of leads that come in each week and I add them to the tracker distribute the leads to sales people based on a rotation. I would like to create a dynamic named range for each sales person that will select each sales person's name including the lead information in the row.
I have read all of the dynamic named range information at the link below as well as the advanced dynamic named range page and I can't seem to put my finger on this short of doing it manually.
View 9 Replies
View Related
Nov 27, 2007
In column C i have dates. I have formula that count all the dates that are older than 20 days. The formula works perfectly. Now i made a Dynamic Named Range of the column A and now the formula results in #VALUE.
View 11 Replies
View Related
Feb 4, 2010
I am using OFFSET to make dynamic named ranges- the only problem is that I'm having trouble using it with linked data-
The Situation:
-Cells A1:H500 are linked to another sheet, with each cell containing a formula like =IF(ISBLANK('C:...[workbook.xlsx]worksheet'!A1),"",'C:...[workbook.xlsx]worksheet'!A1)
-Currently only cells A1:H100 have data, but this is dynamic and will change
-I want a named range that will only select the cells with data in them...the problem is that COUNTA counts the link formula even if the cell is "blank" (because it isn't blank).
-I hate hard-coding ranges...the solution for the named range =OFFSET($A$1,0,0,500-countblank($A$1:$A$500),8) is ugly!
Any solution to making dynamic named ranges with linked data that may or may not be "" ?
View 1 Replies
View Related
Nov 22, 2006
The following formula produces the desired result:
=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!B2:Z100"),"SK")
but replacing the range of cells with a dynamic named range returns #REF!:
=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!Data"),"SK")
where A8 is the date 01/01/07. I'm trying to count items within the range Jan!Data.
I'm not sure if I'm trying to do the impossible, or if I'm missing something.
View 9 Replies
View Related
Apr 3, 2008
I am trying to merge data from two worksheets onto a 3rd for a mail merge. The COLLECTIONS sheet contains the acct #, name and amount owed. The ADDRESSES sheet contains the acct #, name and all of the address information. The 3rd sheet is the MERGE sheet that I'm using as the reference point for my word document and the mail merge. Upon reflection the MERGE sheet is probably not necessary, but made sense to have it when I started out.
Never the less...
On the ADDRESSES sheet I am trying to name a dynamic range "AddressList" (I'm trying to go dynamic because the list of addresses will change from month to month). I am using the following formula in the refers to field when I name the range:
=Offset(Addresses!$A$5,0,0,CountA(Addresses!$A:$A),CountA(Addresses!$1:$1))
I think this is what is giving me my problem, because the named range does not show up in the list when I try to go to the named range.
Just in case that is not the problem, the #Ref is showing up when on the MERGE sheet I type a vlookup formula referencing the acct # on the ADDRESSES sheet. That formula looks like this:
=VLOOKUP(A4,AddressList,3,0)
which seems simple enough. Before trying to make the named range dynamic the formula worked fine, which is why I'm thinking the named range is what's giving me the problems.
View 9 Replies
View Related
Jul 28, 2008
I m trying to make a query based on a dynamic range and paste the results in a sheettab of my wrorkbook.
View 9 Replies
View Related
Sep 9, 2006
I have just started diving into the vast world of dynamic ranges and it's a bit overwelming. I have a dB of sorts that a listbox in a userform fills off of. The dB will be driven by column A and B. Column B are names that are displayed in the listbox. Column A is the class that these names belong to (3 classes). I need to develop dynamically named ranges of Column B based off of Column A. In other words, I need a dynamically named range for all of class1, class2, and class3 in column A to display column B in the listbox on the userform. I have attached an example to clarify the example.
View 6 Replies
View Related
May 30, 2007
I have a spreadsheet with 300+ questions in columns (covering 2 worksheets - rows 1 to 5), and the answers to the questions are placed in the rows beneath - column A & B lists who the answers came from.
The idea here is that on a seperate sheet a user can enter a question number (e.g: 1.2OP007) and a graph will update to show the different values given for each person who answered that question.
So, in this seperate sheet I have the question in cell $A$1.
In cell $B$1 I have this formula:.........................
View 3 Replies
View Related
Aug 7, 2014
I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
View 4 Replies
View Related
Oct 13, 2011
I need to rank the top 3 names for each location based on gender (see sample data table below).
My spreadhseet has been setup using dynamic named ranges ("Size", "Name", "Gender", "Location" and "Score") using the formula "Size"=MATCH("zzzzz",'Sheet1'!$A:$A) and then "Name" =OFFSET('Sheet1'!$A$2,0,0,Size), and so on for "Gender", "Location" and "Score".
Is there a way to design an array rank formula using the dynamic named ranges to return the name associated with the highest score for "M"s in location "N"? I want to be able to edit the formula so I can chose which rank to display (e.g. 3rd, 7th, etc).
I've been playing with the following but can't get it to work
{=RANK(IF(ISNUMBER(MATCH(Gener,{"M"},0)),IF(Location{"N"},0),Score)}
NameGenderLocationScoreTimMN89
RebeccaFN45DanaFN85TerryMN15
RachelFN78BruceMN31BryanMS15
DonnaFS80JakeMS67MelissaFS66AdamMS48KateFS90
View 3 Replies
View Related
