Dynamic Ranges In 2007 Chart

Jul 24, 2008

I created a chart in Excel 2003 which relies on dynamic named ranges. For years this chart has been working beautifully. I recently converted to Excel 2007 and the chart no longer understands the dynamic named range for the x-axis. It understands the y-axis dynamic named ranges just fine. I have tried renaming the x-axis range to various names (none of which contain the word chart). I also experimented by just using a static range name. And I have tried recreating the chart from scratch with the same results. It lets me create the chart initially but then when I refresh the worksheet, the x-axis is blanked out.

ADVERTISEMENT

Chart Dynamic Ranges

Jan 20, 2010

I have a stock chart that I want to update dynamically if the data record length changes. From the source data menu, I tried to use the following data ranges:

=OFFSET(Candles!$AB$8,1,0,COUNTA($AB:$AB)) ---> Date, x values
=OFFSET(Candles!$AD$8,1,0,COUNTA($AD:$AD)) ---> High
=OFFSET(Candles!$AE$8,1,0,COUNTA($AE:$AE)) ---> Low
=OFFSET(Candles!$AF$8,1,0,COUNTA($AF:$AF)) ---> Close

When I place any of these in the data range box under 'x axis labels or values', I receive the message "This function is not valid".

The MS webpage at http://office.microsoft.com/en-us/ex...098011033.aspx describes using these formulas but they don't appear to work.

View 9 Replies View Related

Dynamic Chart/Graph For Changing Ranges

Apr 25, 2008

I have a database in excel with a lot of tables in which lets a uses a combo box so the user select can view just one table.

I want to be able to create a macro so when a button is pressed, it copies the data into a new workbook and creates a chart so they can do whatever they want without disrupting anything in the original workbook.

I have been able to find code that will export the data and create a chart (see below) but the problem is the range changes from table to table so I need to find a way of changing how the chart picks up the data range.

Sub Chart_New_Book()

Sheets("Temp").Select
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

So at the moment when charting ony cells A1:C10 will be plotted no matter what the size of the table is and making the target range bigger only sees the blank cells being plotted.

View 3 Replies View Related

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 View Related

Excel 2007 :: Chart Created Using Name Ranges

Jan 28, 2013

I have built a chart using dynamic nameranges. The problem that I am facing is I tested it on two systems . One system the charts works fine but the other system the charts does not work . Both are excel 2007. Now the error that I get is if I see the chart data source from one system it is showing "!". The other file shows the chart data source as "0!". What should I be checking so that it works in the other system as well?

View 6 Replies View Related

Excel 2007 :: Dynamic Colors In Line Chart

Dec 9, 2013

I have a line charts with values above and below zero.

Im looking for a way to colour the steps below zero in one colour, and above zero in another colour - without doing it manually.

Using Excel 2007.

View 1 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

Dynamic Starting Point For Dynamic Named Ranges

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

Static Ranges And Dynamic Ranges

Feb 26, 2010

definitions for static ranges and dynamic ranges?

View 9 Replies View Related

VLookup For Dynamic Ranges

Oct 28, 2008

I want to create dependent lists using data validation. The lists need to be created from ranges that will be growing as users add more data. I think the best way to create the list is a VLOOKUP. However, I am not sure how to use the VLOOKUP when the range is changing.

I have attached an example. I have a list on sheet "Vlookup" called "FRUIT" with "apple", "orange", "banana". Then to make the depedent lists I have created three other lists called: "APPLE", "ORANGE", "BANANA". I want to pull the COST from sheet "VALUES" into the lists "APPLE", "ORANGE", "BANANA". Users will be adding costs next to the FRUIT they purchase.

View 5 Replies View Related

Dynamic Ranges For Correlation

Nov 18, 2009

I have a particular issue - i am trying to create a rolling correlation.

lets say you have two variables for 3 different months:

Month1 a x
Month2 b y
Month3 c z

correlation will correlate a-c with t-v

however, come next month - i have to manually drag the ranges for a rolling three month correlation:

Month1 a t
Month2 b u
Month3 c v
Month4 d w

correlation will now correlate b-d with u-w for a the last three months.

I want to automate this as I know what the month is, I can't seem to return the last three months and correlate the data.

I have tried to do this with the database functions, but I think that they break the array relationship of b-d and u-w in the correlation function.

View 9 Replies View Related

Darn Dynamic Ranges Again

Jan 20, 2007

In the example I have attached, I need 4 named, dynamic ranges to build a chart with:

1) Dates for the chart
always starting from A2 ... to whenever the LAST sum of B, C, D =0
(right now that would be A2:A30)

2) The Amounts
I need the amounts as 3 individual dynamic ranges, with no 0 values
(example - B2:B21, or c10:c14, etc)

3)The date column
same as #2, but just a single cell reference to show as a marker on the chart.
(example e21:e21)

I know someone here will think 'so what's the big deal' and pop it out in 30 seconds.

View 10 Replies View Related

Defining Dynamic Ranges

Oct 6, 2008

I'm trying to make ranges in a SumProduct formula dynamic, but getting errors #N/A. I think this is because the top two rows are headers, throwing off the range count.

Q. How do I adjust the range definition to compensate., e.g.

Range =Offset(Sheet1!$D$3,0,0,COUNT(Sheet1!$D:$D),1) where column D is numeric, and

Range = Offset(Sheet1!$T$3,0,0,Match("*",Sheet1!$T:$T,-1),1) where column T is text?

I had assumed that the offset value 3 would do this, but I suspect the functions count and match are not doing so.

View 8 Replies View Related

VLookup With Dynamic Ranges?

Feb 24, 2012

I am trying to create a vlookup to get a count of trouble tickets techs completed daily within a table I created, I am using a table since its dynamic. For example I need to see how many tickets Joe completed in a day. See below...

Tech Ticket# Comments Status
Joe 1234 Replaced HD Closed
John 3212 Replace Motherboard Closed
Joe 5678 Installed OS Closed

Above is just an example (my table has 40 columns). I need to vlookup joe and get the count of the closed tickets.

View 6 Replies View Related

Charting With Dynamic Ranges

Mar 25, 2014

So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?

View 2 Replies View Related

Summing Between Dynamic Ranges

May 21, 2014

I have monthly sales, and i wanna be able to sum the last-twelve month sales, when i select the month from a drop-down list. For example, when i select Feb14 from the drop-down, I want the formula to sum the sales between mar13-feb14. how can i accomplish that?

Select Month
Feb14

LTM Sales
?

[Code] ..........

View 7 Replies View Related

Dynamic Named Ranges ...

Aug 20, 2009

Dynamic named ranges - lots of them! ...

View 9 Replies View Related

Consolidating Dynamic Ranges

Sep 21, 2006

I am creating a consolidation worksheet. It contains a macro that automatically copy data from other sources and paste it into my DB sheet. It already works, but I want to improve it. The source worksheet databases has been defined with the name “SOURCE”. Is there any way that I can make my macro look for this dynamic ranges directly, copy and paste it into my DB sheet. I am attaching an example of what my current macro is doing.

View 2 Replies View Related

Autofill Dynamic Row Ranges

Feb 21, 2007

I've got a spreadsheet that I download a list of information into. The information relates to activities that are taking place e.g. start time, end time, type of activity (e.g. meeting) and duration (which identifies the length of the activity in 30 min slots e.g. 9.30am - 11.30am = 4).

The rest of the columns are labelled to represent the time in 30 min slots starting from 9am. What I would like excel to be able to do is to identify the first cell in the range which would be the cell that represents the start time in the row. Then from this cell fill cells in the row up to the end time with something - it doesn't matter what as long as the cell can be differentiated from the other blank cells in the row.

I don't think that I've explained myself that well so I've attached the spreadsheet for you to look at.

View 4 Replies View Related

Dynamic Ranges And Sumproduct

May 2, 2007

I'm having a fit with this formula; maybe the gurus will show me what I'm doing wrong. I have a list of area numbers along with dollar amounts that go with these area numbers. I have created a named range named "Areas" and another one named "MO_PD".

At the bottom of the worksheet I am doing a recap that takes the area number and sums all the entries in the MO PD column. This approach has been done before with Thomach's help. I'm trying to mimic it, but it's not working for me.

NOTE - I know that I can get around this using a pivot table, but I would like to use this approach for now. I would at least like to know what I'm doing wrong. Also, I'm not totally sure if I did the dynamic named ranges correctly so that may need to be fixed.

View 9 Replies View Related

Looping With Dynamic Ranges

Jun 29, 2007

I've been working a macro that seems pretty simple, but has been giving me loads of trouble. I have about 20 worksheets in a Workbook, which all have a similar template (i.e. the data I'm interested in starts on row 14), but have different amounts (rows) of data in them. I want a macro that sorts all the data in ascending/descending order according to column Q. The problem that I am running into is not that the macro only works for the sheet that is active when I run the macro as opposed to every single sheet in the workbook ....

View 4 Replies View Related

Countif With Dynamic Ranges

Jul 1, 2007

Does COUNTIFS not work with named ranges/dynamic ranges? Everytime I try, I get a #VALUE! error. These are my 2 dynamic ranges:

ResultsM=OFFSET([results_list.xls]Results!$M$4,0,0,COUNTA([results_list.xls]Results!$M:$M),1)

ResultsQ=OFFSET([results_list.xls]Results!$Q$4,0,0,COUNTA([results_list.xls]Results!$Q:$Q),1)

The formula is simply:

=COUNTIFS(ResultsQ,"GENDER",ResultsM,"MALE"

However, this does NOT work. it returns the #VALUE! error. However, odly enough, when I use column references, it works:

=COUNTIFS([results_list.xls]Results!Q:Q, "GENDER", [results_list.xls]Results!M:M, "MALE")

This makes me think that COUNTIFS just don't work with named references..

View 8 Replies View Related

Create Dynamic Ranges

Jun 5, 2008

I have just upgaded to Excel 2007 and previously used a Dynamic Range Wizard addin in Excel 2003 but it does not work in 2007. I think it originally come from Robert Bruce but can not find it on the web to get an update.

View 2 Replies View Related

Pivot Tables Using Dynamic Ranges

Jan 7, 2014

Worksheet A - I've set up a dynamic range to display the last 12 row entries of 2 columns
Worksheet B - pivot table of the range

The dynamic range is working perfectly, it displays the correct rows I can set up the data source once... the results on the pivot table display corectly

Problem When i press refresh, the data source "dissapears" and i have to rebuild the pivot table.

View 12 Replies View Related

How To Add Numerous Dynamic Named Ranges

May 6, 2014

I have 153 rows of data to name, with a different offset formula per row.

View 8 Replies View Related

Dynamic Validation Without Using Named Ranges

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

Cascading Combobox And Dynamic Ranges?

Aug 21, 2013

I want to create a cascading combo box from some values that I have, ill explain how I approached it.

Cascading combo box is a combobox which values are obtained depending on the answer of a previous combo box.

So I set up a little table like so:

Categories Car Train Bike
Car Mercades First Honda
Train Ford Western Ducati
Bike Skoda Red Suzuki

and the combo boxes would be set aside from that.I collected the data in Named Ranges (A Range for Categories, A Range for Car, A Range for Train...)

The first data validation box contains = Categories the second data validation box contains =INDIRECT($C$2) (Where C2 was the range of the first validation box)

Once this was working, I decided to change the formula for the named ranges to allow me to add more values.

The formula was: =$F3$F8

I changed it to: =OFFSET($F$3,0,0,COUNTA($F3:$F96),1)

I expected this to work however, it just doesnt.... when clicking on the drop down arrow for the validation box, nothing happens. No box or values or dropdown.

View 2 Replies View Related

Sorting Data In Dynamic Ranges

Feb 6, 2014

In the attached workbook I have hard-pasted data in columns A:M that's designated as an Excel Table to make it dynamic.

In the adjacent columns O:AA I have formulae to extract data from A:M based on the "Frequency" stipulated in cell AD1,

I would like to be able easily to sort the data in the variable sized range in columns O to AA in descending order of the calculated values in column Z.

Best way to do this so that the formula don't get jumbled up or the blank cells in column Z end up at the top of the sorted data?

View 2 Replies View Related

Sort Data With Dynamic Ranges

Jun 8, 2014

I currently have two tables in one worksheet showing the sales of different region.

The problem is, when I sort the data in the table (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??

**Attached, please find the example spreadsheet, I have only written the code for the first table.

View 3 Replies View Related

Editing Macro To Allow Dynamic Ranges?

Oct 31, 2011

having trouble editing the code to allow for dynamic ranges. I have tried to research online, but am having a hard time with it. I think I would understand better if I could see a practical example.

Here is the sample macro I recorded in Excel that I am working with:

Sub Macro1()
'
' Macro1 Macro
'
'
Cells.Select
ActiveSheet.Range("$A$1:$C$87").RemoveDuplicates Columns:=Array(1, 2, 3), _
Header:=xlNo
End Sub

How can I edit this to allow for dynamic rows and columns?

View 2 Replies View Related

Selecting Dynamic Ranges In Macro?

Nov 30, 2011

I run a macro on a spreadheet in which the rows increase daily. In the macro I simply recorded an end.down to select a column of data which ends up being fixed row numbers in the code so the next time I run the macro it only selects the range I selected last time.

View 1 Replies View Related

VBA Advanced Filter Dynamic Ranges

Jul 24, 2014

The range element below needs to be dynamic. A1 and down meaning if i was to do this out of VBA I would hold CTRL+SHIFT+DOWN but i cant do that from macro record as it doesn't work, I'm guessing this Range("A1:A73") is the element I need to change but I don't know what to?

Range("Main[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Input").Range("A1:A73"), Unique:=True

View 3 Replies View Related

Non-Volatile Dynamic Named Ranges

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

Advanced Dynamic Named Ranges

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

Use Macro To Create Dynamic Ranges

Jan 28, 2010

I am looking for a macro that will allow me to create a dynamic range based on the settings I give it. I would like the dynamic range to be called tracking_number and it to have a reference to =Offset(MainReport!$A$2,0,0,COUNTA(MainReport!$A:$A),1)

If this is possible I would really appreciate it. The reason I need this is because I have 2 workbooks. They are the one with the MainReport in it and then the Master workbook. Every day the workbook containing the MainReport is overwritten with more data, so I can not store a dynamic range and reference it each day.

View 9 Replies View Related

INDIRECT And Dynamic Named Ranges

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

Multiple Dynamic Name Ranges In Same Column

Apr 24, 2007

I have a huge database. The rows are broken up by the groups. The columns are broken up by months, quarters, and totals. All the named ranges are the same exact size because all groups have the same category names and all columns have the same amount of months and quarter columns. So it sort of looks like this

Actual 2006-----------------------------Actual 2007
Group Category Jan Feb Q1 Total-------Group Category Jan Feb Q1 Total
100 Labor Cost 220 130
100 Labor Cost-Expense
Group Category Jan Feb Q1 Total
101 Labor Cost
101 Labor Cost-Expense

Hopefully this gives you a good idea of what the spredsheet format is. Right now I have named ranges for all the groups and years. So the top left name is A06_100, then it goes A06_101, etc. What I'm trying to do is set this up as easy as possible to insert rows in the named ranges and keep the named range, because they are referenced a lot in other worksheets. Also if any columns get added. Basically I want it as user friendly as possible so when people change things it stays together.

View 2 Replies View Related

Multiple Criteria Sum With Dynamic Ranges

Dec 11, 2007

I have a sum part of which is $I$4:$I$500.

The 500 part of the sum is constantly being manually changed using edit replace because of a growing amount of data.

In cell A1 ona another worksheet within the workbook I use the Count function to count the number of rows containing data. This cell is named DataCols

Is it possible to combine $I$ with the named Range DataCols?

I have got this far - ="$I$"&INDIRECT("ColNo") which returns 500 if I remove "&I$"& in front of it.

View 6 Replies View Related

Advantages VS Disadvantages To Dynamic Ranges

Dec 23, 2007

I understand the advantages to dynamic ranges, but what are the disadvantages?

For instance, is choosing an entire column slower in calculating than, say, 100 columns?

=offset($A$1, 0, 0, counta($A:$A), 1) versus

=offset($A$1, 0, 0, counta($A:$100), 1).

If it is slower, are there other disadvantages I should be aware of?

View 5 Replies View Related

Multiple Dynamic Named Ranges

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

Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!

Dec 19, 2008

My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.

So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.

This solution also worked perfectly, but now I've been told to create the graph without macros.

Does anyone have any suggestions? The requirements/details are as follows:

1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels

Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?

View 9 Replies View Related

ADVERTISEMENT