Dynamic Table Referencing?
Mar 16, 2014
I am trying to pull data from different Tables within a workbook by typing the name of the referenced Table in a cell rather than leaving it static withing a VLookup formula.
So far I have this:
=VLOOKUP($B6,OctTable,MATCH(D$4,$D$4:$V$4,0),0)
What would I do if I wanted to be able to dynamically change the "OctTable" portion with the name of another table which I type in call A6? I have researched as best possible and think that it might have something to do with INDEX, but I have failed to figure it out.
View 1 Replies
ADVERTISEMENT
Jan 3, 2014
I'm attempting to list my data... I have a dynamic table that has the following columns:
number of countries
country 1
country 2
country 3
1
Egypt
3
USA
Egypt
Scotland
2
Scotland
USA
As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.
I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.
The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"
I found the following formula (in E2):
Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)
Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.
View 1 Replies
View Related
Dec 1, 2009
I have a worksheet titled "Systems Estimate" that will dictate values on a second worksheet called "CSI Estimate". They're the same values in each cell and column, just in a different order row wise. I would like the values in each row in the A column in the "CSI Estimate" sheet to dictate columns B through W, with the values being referenced from the "Systems Estimate" sheet. I tried using an exact match with vlookup since the data isn't sorted, however it just returns the formula I enter, not a value.
View 3 Replies
View Related
Feb 16, 2010
1. I have a worksheet with subtotal and total row above all columns
I currently use SUBTOTAL(9, A6:A6000) and SUM(A6:A6000)
however the no of rows is dynamic based on imported data so I want the range end (i.e. A6000) to also be dynamic i.e. refer to the last cell with data in the column.
Something like SUBTOTAL(9, A6:LastCell in A)
2. I also use SUMPRODUCT to do calculations
e.g. SUMPRODUCT (($B$6:$B$6000 = "ABC") * ($A$6:$A$6000))
again how can I make the sumproduct dynamically use all rows to the last active row.
3. I have tried just setting the second part of the range reference to a very large number but am worried that this slows down the calculation. Does it?
e.g. SUMPRODUCT(($B$6:$B$100000 = "ABC") * ($A$6:$A$100000))
View 9 Replies
View Related
Mar 2, 2014
I'm trying to reference text from one worksheet to another. The data is all in column A on the "Template" worksheet, however the row varies (ie starts at row 5, then 7 the 9 etc). The cell row is always +2 rows on the previous row, and column A.
What can I use to pull across the cells with the information that I want, onto the "Report" worksheet?
View 3 Replies
View Related
Jun 15, 2012
I have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!
I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.
=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)
View 1 Replies
View Related
Oct 31, 2013
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
View 1 Replies
View Related
Jun 26, 2014
I have a spreadsheet with two sheets in it.
Sheet 1 = sheet with formulas/functions for data analysis
Sheet 2 = sheet with table
I am trying to reference a column in the table on sheet 2 from sheet 1. Specifically, I am trying to set up Data Validation that will create a list of unique values from a column in the table. I tried:
[Code] ......
There is a space in the header name of the column. but I was unable to get the validation to work even with columns that have just a single word name.
I also tried:
[Code] .....
and that did not work either.
View 3 Replies
View Related
Aug 20, 2014
The macro I have has 2 arrays that at present has a representative sample of the data I will be looking for and hence allocating a relevant code. I thought the if MyArr was now Sheets("Sheet2").range("A1:A200) and similarly for MyAssettype using column B. however i just get a Runtime error!
View 5 Replies
View Related
Sep 24, 2008
On XL2003 i am putting together a worksheet that has a list of tables by month i.e. a table for January then a table for February below it and so on...
Within each table the data is referenced as follows:
=SUM('V:Product Testing InformationAuto final test stationsTest ResultsSAP Files[Test Data070801.xls]Sheet1'!$AV$18)
How can I change the highlighted date to reference a cell containing the date? i.e.
=SUM('V:Product Testing InformationAuto final test stationsTest ResultsSAP Files[Test DataA2.xls]Sheet1'!$AV$18)
In cell A2 would be "070801"
View 9 Replies
View Related
Oct 19, 2011
I have a static table that contains a list of all posible components for a particular product that we sell. The sheet successfully calculates the qty. of each component required for a particular install.
What I would like to be able to do is to create a new list able from the static one that only shows the components that are needed i.e. those that don't have a zero qty count.
I have seen some examples that come close on YouTube but can't find anything that does exactly this.
View 6 Replies
View Related
Sep 26, 2012
I have a worksheet ("VillageReport") that contains several pivot tables. All tables are from the same data source and all pivot tables have "Location" as the first column field. I would like for the tables on this sheet to automatically be filtered when I select a village name from a drop down menu in a cell on this worksheet (C1). So, for example, when I select "Tarzana" in C1, the location field of all pivot tables on this sheet will show only Tarzana (all others are deselected).
a Macro for this? (I have already set up the drop down menu in C1). I have found some examples online but can't seem to adjust the code to fit my workbook. I am not very familiar with VBA.
View 9 Replies
View Related
Nov 30, 2009
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
View 13 Replies
View Related
Jan 21, 2010
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies
View Related
Jul 10, 2009
Is it possible to have dynamic sorting in a table?
What I am after is a scoreboard type effect where as results are entered, the teams are sorted automatically, rather than entering all of the details and then doing a sort afterwards.
I have attached a table where I would like the sorting of columns B - E done in the following order (All decreasing order):
Comp Points first
then by Sets %
then by Points %
I am drawing this info from another table. Will this have an effect on if it can be sorted dynamically? Hoping that as i enter a result in the other table, it will bring across the current comp points, set % and points %, and then sort the table into the rankings all at once.
View 10 Replies
View Related
Aug 28, 2008
I have a spreadsheet the includes data validation, auto fill fields using formulas and conditional formatting all over the place. The user will be adding rows of data whenever applicable, so I want to create a dynamic pivot table that will update automatically. I think I've done it properly, Insert - Name - Define. In the Refers To field, I've added:-
=OFFSET(Sheet1!$A$10,0,0,COUNTA(Sheet1!$A:$A),12)
I've then based the Pivot Table on the defined name. For some reason, when I add a new row of data, the pivot table doesn't update, I have to do it manually. When I go back into Sheet1 and click in the Refers To field, there is an extra "blank" row showing as included in the data range (screen print attached) Is my OFFSET wrong, or does it have anything to do with the fact each row (down to line 300) contains formulas and #N/A's?
View 10 Replies
View Related
Jul 28, 2006
able to create a table that is derived from a pivot table (see attached file). The pivot table represents sales amounts for each company in each period. And can be drilled down on country and category. The table that is derived from this pivot represents the market share percentages. In the attached file in filtered sales data from country "UK" and the pivot table only shows the active companies in this market. However in the derived table all companies are still represented but inactive companies are now represented by "#REF" values. Is there a way to exclude inactive companies in this derived table? If so, how can i make this derived table dynamic?
View 6 Replies
View Related
Jul 24, 2007
I have a large table where rows are added by copying either the top row or the bottom row and adding it to the bottom of the table. Below is the coding I was hoping to use to jump from cell to cell within the rows using a hotkey assigned to the macro.
Is it possible to apply the following code in a way that copies the same hotkey targets from row to row?
In other words, a row copied from row 1 to row 2 would still have functioning targets of A2, D2, L2 and Q2.
Select Case ActiveCell.Address
Case Is = [A1].Address: [D1].Select
Case Is = [D1].Address: [L1].Select
Case Is = [L1].Address: [Q1].Select
Case Else: [A1].Select
End Select
View 3 Replies
View Related
Oct 15, 2012
I am trying to filter a table of dynamic size. My table begins at P3 (first row of data, not column header) and ends somewhere at the bottom of Column AA. I am getting an error "AutoFilter Method of Range Class Failed." Why? How do I fix this?
Here is my code
VB:
If Target.Range.Address = "$B$4" Then
With Sheets("Days Past Due")
LastCell = .Cells(.Rows.Count, "AA").End(xlUp).Row
MsgBox LastCell
ActiveSheet.Range("P3:AA" & LastCell).AutoFilter Field:=17, Criteria1:="MABST"
End With
End If
View 3 Replies
View Related
Oct 12, 2013
I am having a hard time creating my ultimate gradebook. Right now, I am able to hide a SPECIFIC table on a SPECIFIC worksheet using the following code on a macro button:
VB : Sub HideRow()Range("Table2[#All]").EntireRow.Hidden = True End Sub
The problem is that this sheet will be a template and as a new user inserts a new sheet, I would like to copy and paste this template to each new sheet. Since the Table values change with each new sheet, code wont work on the new tables. How I can name each new sheets table "Class Data", and the macro button to hide will work on each respective sheet?
View 2 Replies
View Related
Sep 15, 2014
I have a table with outstanding days for Funds owed by multiple accounts which dynamically changes everyday I am looking to group this table by >5days, 6 to 14 days , 16 to 40 days ,>40 to 90days and >90days consistently even though the table will change dynamically every day..
View 4 Replies
View Related
Jun 23, 2014
I am trying to make a dynamic pivot table using VBA. Unfortunately, Im not very good.I have a set of data with variable number of rows and variable number of columns and I need to make a pivot table. Need to select this data from sheet - Master, where the first data entry starts in cell A1. of the data in the sheet, i only need the name and age title In the pivot table fields -
ROWS needs to contain - name
COLUMN needs to contain - Age
Values need to contain - count of Age
After this is done, since the data will be dynamic, the pivot table will also be dynamic. i need to copy the pivot table data and paste it in another sheet so that I can do some filtering. The filtering part I think i can do my self.
View 3 Replies
View Related
Nov 8, 2011
I've a macro that generates a pivot table from another excel workbook, however i need the range to be dynamic as the excel workbook im generating the pivot table from changes in number of rows on a weekly basis. Here is the existing coding:
Code:
Sheets("QA").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"QA!R1C1:R22580C12", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10
how to make the SourceData dynamic
View 1 Replies
View Related
Mar 27, 2013
I have a sheet in my workbook called Results. This sheet contains a list of a fixed set of columns (A - N) but with a variable number of rows.
I am trying to create a macro to create a pivot table from this data, which automatically selects all the rows in the Results sheet to form the data set.
I've had a look at some of the other threads on this topic, but just can't get this working.
I have the following code that I have pieced together from other threads - you will see I am trying to set "PivotRange" as the data set and include this as the data source in the pivot. I get a subcript out of range error when I run this.
Dim PivotRange As Range
PivotRange = Range(Worksheets(Results).Cells(1, 1), Cells(lastRow, lastColumn))
Sheets.Add
[Code].....
View 1 Replies
View Related
Jul 23, 2013
I'm trying to create a dynamic sum formula off of a table of data which is then used in pivot tables and pivot charts. What I would like it to do, is show me the summation of values AFTER a filter has been applied. Everything I've tried returns a sum of the entire table (includes hidden cells). Perhaps I'm searching with the wrong query terms (this not called a dynamic sum formula?)
Using slicers and pivot charts, I need my account managers to be able to look at their territories quarterly performance vs. a "fair share" of company performance during that quarter. I.e. they would select the slicer for their region, and dates (ex april, may, june) and the pivot chart would show territory performance during that time, with a second column chart series which is company total from april/may/june (my dynamic sum) divided by the 8 territories...thus giving me what should be "fair share" during that quarter. Each territory has equal Opportunity, so this would allow them to see who is over-performing and underperforming in their territories.
View 2 Replies
View Related
Jan 6, 2009
Because my pivot table is used by several users the data source changes often. In response, I created and applied a dynamic range using the offset formula. While the pivot talbe grabs the correct # of records, it inserts a blank column and row. There are NOT any blank cells in the data source range, as reflected by pivot table counts for the Blank column and Row. Any idea why Blank is showing up in my pivot table and how I can correct while maintaining the dynamic range?
View 9 Replies
View Related
Aug 28, 2006
am working on a spreadsheet that requires sorting a list of names and numbers. The numbers have a sum formula attached. I can set up the macro to sort the list fine by selecting the appropriate cells and creating the macro.
The code for this is below
Sub SurnameSort()
'
' SurnameSort Macro
' Sorts by mechanic surname
'
'
Range("A10:H13").Select
Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
However, I want the macro to still be able to sort the data when I add new rows to the list later on. In other words, the code above in bold somehow needs to be modified so that "H13" extends as far down as needed.
View 7 Replies
View Related
Nov 16, 2006
I wish to create a Pivot Table. It is based on source data with fixed columns (A to W) and dynamic rows. The following is my
Dim WS As Worksheet
Dim DataRange As Range
Set WS = ThisWorkbook.Worksheets("sheet1")
DataRange = Range("A1:W1", Selection.End(xlDown)).Select
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=WS.Range(DataRange)).CreatePivotTable _
tabledestination:=Sheets("sheet2").Range("A1"), tablename:="PivotTable1", defaultversion:=xlPivotTableVersion10
Dim PT As PivotTable
Set PT = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1")
PT.PivotFields("Type of Work").Orientation = xlPageField
PT.PivotFields("Profit Center").Orientation = xlRowField
PT.PivotFields("B/(W) CTD Net Rev").Orientation = xlDataField
Because I'm going to use it in my work, it must be reusable. And every time I use it, the numbers of row are different. Therefore, I made the source data into a dynamic range. The range changes when rows change. However, problem always appears in the following line:....................
View 2 Replies
View Related
Dec 29, 2006
I m trying to make an Excel Charts that would update according to the choice made in a control box. I have uploaded the example test.xls. If you open the file, you ll see the graph is link to product 1. What I d like to do is if I choose product 2,3 or 4 from the control box, the chart updates itself to the good corresponding product.
View 2 Replies
View Related
Oct 1, 2007
I have a userform to be populated with data from a dynamic table for purposes of showing users their daily stats. The data is populated on the worksheet in the following way- ..............
When the userform is pulled up, it needs to show the following details -
UserName - User1
Workitem 2 : 12
Workitem 3 : 7
As the work items go all the way to 65, I wanted to only show the work items that had actually been worked on by each person - What I am struggling with is how to populate the textboxes on the userform with this data - without getting into an absolute mess of if statements -
If Range(WorkItem1Range).Value <> "" Then
userformStats.WorkItemA= Range("a2").Value..........
View 2 Replies
View Related
