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
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 RelatedI am having an issue with dynamic charting using named ranges in excel. I quite often create dynamic charts using IF and OFFSET formulas to check conditions in order to create charts of data based on user inputs.
The issue I am having is with linking multiple tables of data in a single named range. In order to make this spreadsheet easy to maintain I am trying to take 3 separate databases and link the spreadsheets through named ranges. The formula I have written is as follows:
=VLOOKUP(VALUE(LEFT(OFFSET(Processed!$B$1,1,0,COUNT(Processed!$C:$C),1),6)),Raw!$F:$O,10,FALSE)
For context this is;
=VLOOKUP(VALUE(LEFT(OFFSET("Serial number I am referencing",1,0,"Qty of rows containing data",1),6)),"Value I want to return from separate table",10,FALSE)
[The formatting of the serial number between the two sheets is slightly different but they share the first 6 digits (hence the wrapped VALUE and LEFT formulas).]
This formula is working perfectly except that it doesn't store the entire data table in a single cell, it only returns a single value. If I enter the formula into a cell and drag it down it returns all the information correctly, however if I create a named range using the formula and try to chart it only the first value returned is charted.
Any way to modify the formula to store the entire data array in the single cell value so it will chart when used as a named range?
I have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..
View 6 Replies View RelatedI have developed an evaluation algorithm for residential mortgages, and am having difficulty testing. I am trying to ensure that my scoring model makes sense from every possible scenario. The grading model takes into account multiple factors that most financial institutions would find important when assessing a potential borrower for a loan. Examples of these factors could be an individual's credit score (ranging from 0 - 800), the type of dwelling (multiple options to choose from), etc. In total there are about 20 different factors, each with a minimum of 5 possible options to choose from.
I am trying to create a spreadsheet that lists out all possible combinations of the multiple factors, and trying to input it by hand . To give an example:
Credit Score
Property Dwelling Type
Property Value
100
Townhouse
$350,000
[Code] .....
I have three rows that each attempt to pull in data from a range on different worksheets. All but one return nothing but "#N/A" values. The one row returns values from the proper range. (User selections determine which row will have data).
Second, I successfully pull data from the row containing actual information into another row, using the following formula: "=OFFSET(E$36,CHOOSE($Y$1,0,1,2),0)" on a cell-to-cell basis. The value in $Y$1 chooses the row to look at, based upon a dropdown selected on another worksheet.
So I now have a row with =OFFSET(E$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(F$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(G$36,CHOOSE($Y$1,0,1,2),0) and so forth. I can find the last value in the row, but I cannot find a way to extract the address from that, and create a table that will use as a series the last 26 values in the row.
For reference, I use "=LOOKUP(9.99999999999999E+307,E49:FD49)" to determine the last cell with a value (gleaned from a Dueling Excel Youtube Video, - very helpful!).
I hope the above will be clear enough, but if not, I'll be happy to provide additional information / clarification.
I have several dynamic charts using named ranges, but I am charting 1 series of data. What about when I have multiple data sets or series and do not want to use PivotTables? Do I really have to make each one a named range? If that's the case, using VBA looks much simpler.
View 2 Replies View Relatedi have 2 worksheets. one with portfolio stock data, and a second that i would like to use for dynamic charting. the chart worksheet has a drop down menu that chages a refernce cell to a number corresponding to the collumn in the portfolio worksheet for the chosen ticker
i would like to make a dynamic chart that updates the data ranges based on something like this: ....
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
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 Relateddefinitions for static ranges and dynamic ranges?
View 9 Replies View RelatedI 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.
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.
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.
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.
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.
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] ..........
Dynamic named ranges - lots of them! ...
View 9 Replies View RelatedI 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.
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 RelatedI'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.
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.
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 RelatedDoes 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..
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 RelatedWorksheet 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.
I have 153 rows of data to name, with a different offset formula per row.
View 8 Replies View RelatedI 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!
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.
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?
