Indexing With Mutiple Results - Dynamic Source
Mar 28, 2009
I was able to come up with a formula that returns multiple results when indexing rather than just the first match.
Now the problem I have is that my source table is going to vary in size. My source table in the formula I got working below is a dynamically named range 'ImportedData'. The data in this table will always start on Row 8 but the last row of the table will vary, so the bold/red areas of the formula need to be dynamic.
how I might modify this formula to allow for the dynamic table size?
=IF(ROWS(B$19:B19)
View 9 Replies
ADVERTISEMENT
Jul 27, 2007
In a workbook that I am working on, a macro runs to import data onto 9 separate sheets. The data is essentially x- and y- coordinates of a plot profile from another program. The problem that I am running into is that the number of data points can vary each time the data in imported based upon how the data was acquired.
If I were to put a drop-down list (Method1, Method2, Method3, etc.) on the Instructions page that I have for this workbook and have the user select which method was used prior to running the macro, could I then incorporate some sort of IF statement in the macro to vary the Source Data Series for the charts that I have created? Right now, I have the Source Data covering the scenario with the longest list of data points so when fewer points are used, the graph looks essentially useless.
View 5 Replies
View Related
Apr 17, 2014
I have a table of company data, and I want to be able to select different companies data and create a chart automatically perhaps by pressing a 'create' button.
The table data contains three values I want to populate in the chart: red & green sales which I would like shown as a stacked bar, and red as a percentage of total sales which I would like on a different axis as a line.
I tried to create a macro but my VBA skills are almost non-existent.
View 2 Replies
View Related
Sep 26, 2011
I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.
These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?
View 5 Replies
View Related
Jul 18, 2012
Is it possible to set the input Range of a dropdown control to be a column from a table?
View 2 Replies
View Related
Jun 16, 2008
I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:
Sub Update_Center_Chart()
Sheets("Center Data Chart").Select
ActiveSheet.ChartObjects("Center Data").Activate
ActiveChart.SetSourceData Source = Range("CenterData")
End Sub
I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.
View 5 Replies
View Related
Feb 9, 2012
I am trying to create a simple xy graph in Excel 2010 using dynamic source data.
I have data in three columns.
There are headings on line 2 and the data starts on line 3.
A B C
1.
2. dp ss ff
3 .1 .2 .3
4 .3 .4 .4
5 .4 .8 .7
6 .1 .6 .8
7 .3 .5 .1
8 .5 .4 .4
I want to create an XY chart with C3 to C8 as the X axis (or dynamic to what ever the last data point is) with A3 to A8 as the Y axis (or dynamic to what ever the last data point is).
I have Defined names for C3 to C8 (myXvalues) and A3 to A8 (myYvalues).
I have created a simple XY graph and selected the ranges as normal.
This is =DATA(,Data!$C$3:$C$9,Data!$A$3:$A$9,1)
The page is named DATA
What I am trying to do now is to edit this range to make the range dynamic.Unfortunately I get error messages which ever way I try to edit the range.
I have tried:-
=SERIES('Data'!$c$3,'Data'!myXvalues,'Data'!$A$3,'Data'!myYvalues,1)
=SERIES('Data','Data'!myXvalues, 'Data'!myYvalues,1)
=SERIES(,Data!myXvalues,Data!$myYvalues,1)
=SERIES(Data!,Data!myXvalues,Data!myYvalues,1)
The data range can go from 8 to 30000 hence the need for a dynamic range!
PS: Also, is there any way to create this graph and dynamic source data in VBA. I have tried this with no success.
View 2 Replies
View Related
Apr 9, 2008
Ultimate goal is to automatically update the source data for 4 pivot tables that are on 1 worksheet. The data for those 4 pivot tables are on 4 different worksheets w/in the same workbook. Consolidating into one dataset is not an option.
I'm familiar with a dynamic named range, but the 4 worksheets that contain the data are replaced daily via automated Access output which creates an error.
I know how to do this adhoc by matching the pivot table names with their respective worksheets, but there are many other documents with similar layouts where this would beneficial.
Below is an example of how I currently update 3 pivots on separate worksheets w/ the same data range which is w/in the same workbook. My proposed changes are below the current. Any ideas on how to return the workbook name as a string...Or am I going about this the completely wrong way...
View 8 Replies
View Related
Jun 19, 2008
Is there a way to make a chart adjust automatically when you add to the source data? I'm trying to use a named range in order to power a chart (something like chart_data=offset(a1,,, counta(a:a),counta(1:1)); the range is working fine). When I put "chart_data" into the source field for the chart, it seems to work fine; it picks up the right range... But when I later add more data, the chart source data field seems to have converted my named range into a hard-coded range, absolute range. So the dynamical named range updates, but the chart doesn't and I don't see the new data. Am I doing something wrong that I need to tweak? Does what I'm trying to do just not work? Is there another way to make the chart pick up the new data without adjusting the range?
View 3 Replies
View Related
Nov 28, 2006
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
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
May 18, 2007
I have a configurator that looks up parts in a table based on several search criteria. The results are listed in individual rows with the number of matches varying per lookup combination.
Is it possible to dynamically add a button or similar to each row that would copy the lookup results of that row to a separate quote form?
how can I predefine the function of the button?
View 6 Replies
View Related
Aug 25, 2014
how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
View 2 Replies
View Related
Sep 22, 2009
find a row containing text. Like when opening a PDF file with Excel, all the pdf code is in column A. I would like to create a formula that would search column A for the text "Example" and have the row number be the result.
I've tried using index, match, find, search, etc.. and nothing seems to work.
View 5 Replies
View Related
Oct 22, 2007
I have to find the maximum value of a column (J16:J115) and refer to the name in column A corresponding with the number found... everything works perfectly with :
=INDEX(A16:A115;MATCH(LARGE(J16:J115;1);J16:J115;0))
But when there are two maximum values it only gives me the first one... is there a way I can get both (or more) names in different cells ?
View 9 Replies
View Related
Jan 6, 2010
In the attached file the
„«All the data is available in the details sheet
„«Input field is B20.
„«There are 6 Rate Schemes. All the input fields are present in all
„«Again there are 3 Subline of Business
Need .. when I put a input field and select a C15 and C17 the Exposure Curve, Flexa, Other should get displayed.
Hope I made the scenario understandable.
View 2 Replies
View Related
Dec 9, 2008
I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.
indexing a range from another spreadsheet, but when I tried to use it again on a different range & copied & pasted and changed the ranges, I get #REF!, which I know means I screwed something up, I'm just not sure what & I don't know what the 1's & 0's mean.
=INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))
View 7 Replies
View Related
Jun 3, 2009
I have two worksheets that are formatted differently. One has a list of employees and the other has the same list of employees but with associated employee IDs. I need to pull the employee IDs to the other sheet.
Let's call the two worksheets A and B. On Worksheet A (the one I'm working with), here's the format (column A):
Employee Name
Doe, Jane
Doe, John
Roberts, George
In worksheet B, here's the format (column A | B | C):
Last Name | First Name | Employee ID
Doe | Jane | 1105
Doe | John | 1106
Roberts | George | 1107................
View 9 Replies
View Related
Aug 22, 2006
In the attached sheet, I'm trying to get excel to return the top 5 values in an array, based on the preceding cell. This example is for a Football League, in which the QBs, RBs and WRs all have different salaries. The objective is to return the salaries of the top 5 paid people in each. Don't need to return the name, just the salary amount, so that an average can then be taken to figure future salaries.
View 5 Replies
View Related
Feb 23, 2014
I am trying to create a form where my staff can use a drop down box to select their role on the drop down bar on the "Template" tab- M12 (highlighted orange) that can reference to same role on "sheet 2" and show the corresponding data in column C, highlighted in green. The output of the formula starts on "Template!23" - highlighted in yellow. I have started to use an array formula but cannot fully utilise it as I need the row reference needs to be variable. Is it possible to use multiple "ifs" to account for the changing inputs on the drop down bar? What do you think? <attached>
View 5 Replies
View Related
Nov 11, 2009
I am trying to index and match information and trying to show the multiple values that go with it. Then I am trying to do the same with vlookup.
View 4 Replies
View Related
Nov 2, 2013
indexing complete data sets for each entered value. see example to better understand what I am trying to do. I have been trying for weeks.
Steve
INDEX.xlsx
View 14 Replies
View Related
Dec 10, 2009
I'm trying to improve a code that Indexes a series of prices to a day (the first day of a range). I'm doing this to numerous named ranges. The code that i build takes ages to do this:
View 9 Replies
View Related
Jul 2, 2014
I'm trying to get a document together that will spit out an index of all the files in a folder/subfolder with various attributes like title name/ hyperlink to the file/ type/ etc. How to add a function that will create a column/pull data for the author of the files I am indexing.
Code:
Public fPath As String
Public IsSubFolder As Boolean
Public iRow As Long
Public FSO As Scripting.FileSystemObject
Public SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Public FileItem As Scripting.File
Public IsFileTypeExists As Boolean
[Code] ...........
How to get a column that lists the author of the document.
View 2 Replies
View Related
Aug 29, 2008
I have a folder with about a thousand files in it, each one holds one worksheet.
What i need is a new file which lists all these file names in column A and, in column b, the value from cell A1 in each file against the file name, i.e.
book1 Apple
book2 Banana
I can list the files in the folder using dos, but cant rip the a1 cell values out,
so I need to open each workbook in turn, record the file name, and then record the value in cell A1 against it.
View 9 Replies
View Related
Nov 24, 2008
I would like to create and Index/List of all the Hyperlinks in a workbood and the locations of those links. It can be a little as just the tab/worksheet the hyperlink is located. I can then import those links into a spider to check to see if they are broken or not.
The following code is close, but I need it to loop through the workbook and list the locations as well.
Sub test()
For Each h In ActiveSheet.Hyperlinks
With ActiveCell
.Value = h.Address
.Offset(1).Activate
End With
Next h
End Sub
View 9 Replies
View Related
Apr 1, 2009
Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?
View 5 Replies
View Related
Feb 6, 2014
I copied and pasted text (last names) from Website A into File A. Then did the same from Website B into File B. I wrote index match formulas to compare columns between files. I've done this literally hundreds of times, and it's always worked. This time, however, I get ZERO matches - even though there are lots of them. Troubleshooting was straight-forward: it has to be website B. (Strange thing is, I HAVE used text from website B before in similar applications with no problems.) Something in the way they present the data is preventing matches. (Everything works when I manually type over text from Website B.) It's not an upper case/lower case problem. I tried copy-pasting values only - to no avail.
View 4 Replies
View Related
Oct 10, 2013
What is wrong in this Indexing code.
Code:
Range("R6").Select ActiveCell.FormulaR1C1 = "=IF($Q6="","",INDEX('Worker-Exempt'!B:B,MATCH($Q6,'Worker-Exempt'!A:A,0)))"
I get application 1004 error msg.
Cross Posting
HTML Code: [URL] ........
View 9 Replies
View Related
Dec 16, 2008
i am trying to lookup up when ppart matches spart and subtract total shipped from total produced. Here is where I am getting stuck .... because spart has multiple returns LOOKUP(2,1/((I2=C:C)),J2)
View 3 Replies
View Related
