Stack Overflow
Stack Overflow

Questions tagged [structured-references]

Structure references define rectangular sections of tables using special syntax, table column names, and reserved keywords instead of relative or absolute references. Structure references do not need adjustment if tables they refer to have been modified. They also provide a mechanism to define areas using meaningful table column names instead of less helpful cell references. --source MSDN

23 questions
16
votes
1 answer

$ (dollar sign) equivalent for structured notation (Excel Worksheet table)

I'm basically looking for a $A:$A equivalent for structured table references in Excel. Say I have this formula: =INDEX(tChoice,MATCH(OFFSET(tData[@[cm_sex]],-3,0),tChoice[name],0),3) Basically tData is a table full of raw data (many columns), taken…
logicOnAbstractions
  • 2,178
  • 4
  • 25
  • 37
2
votes
1 answer

Excel: AND function with structured references

When using Excel structured references, Assume this table (MyTable): If I enter the following in a cell, I get 5 values: =MyTable[Col A]>2 ...and if I enter the following in another cell, I get 5 values: =MyTable[Col B]<9 However, If I want to…
MikeB
  • 21
  • 1
2
votes
1 answer

How to convert dynamic (structured) reference in Excel to fixed cell value?

I am loading a table into an excel file from a separate file - from this import table I would like to create "sub-tables" defined just by specific columns. Therefore I am using dynamic referencing to see only parts of these tables on a different…
Jan Tásler
  • 23
  • 4
2
votes
1 answer

Excel structured reference with variable name

I have a table TABLE and two cells E1 and E2. The entry of E1 is guaranteed to be a header of TABLE. I'd like to write a formula to check wether the entry of E2 is contained in the table column TABLE["content of E1"]. My current approach looks like…
s1624210
  • 627
  • 4
  • 11
2
votes
1 answer

Structured reference changes to absolute when copied across sheets

I have a monster of a workbook that I'm trying to make more manageable for those that use it after me. I have a ton of code that is ran when buttons are pressed to make it more user friendly to those that know little to nothing of Excel. So here is…
zcr07
  • 27
  • 2
2
votes
2 answers

Return multiple items from a table

I have an Excel file with two sheets. In "Sheet 1" I have data that is formatted as a table. It has three columns: MyData[Name] MyData[Month] MyData[sales] On "Sheet 2" I want to select multiple items from MyData that match a…
Ilana
  • 23
  • 3
1
vote
2 answers

Using Excel table structured references to return a specific cell based on value in another cell

I have an excel table, lets call it Table1. There are 3 columns, Column1 Column2 and Column3. Potentially there could be 30-50 rows. I would like to get the last cell index in Column2 based on the value in Column3. For example, I am looking for the…
Ravenerabnorm
  • 11
  • 1
1
vote
1 answer

Excel Structured Reference Dynamic Table Name

I've been at this for an hour now and I've ended up going back to using INDIRECT, which I would rather avoid. I feel like there must be a simpler way of using table format to get this done very cleanly, but I can't figure it out! I have a sheet like…
John Owens
  • 61
  • 6
1
vote
1 answer

Structured References: Absolute and Relative addressing

This is a "weighted average" formula where Q14:Q21 contains the "weights"; Column C is the first column of data, and subsequent columns are D:P I can use this formula, and fill right. The data columns will change and the "weight" column remain the…
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0
votes
0 answers

Excel does not get the Structured Reference when i manually select it, but references do exist

Good day, yesterday the workbook worked just fine, today the Structured Reference in any object doesn't work, in example picture 1 I select with the mouse or arrow keys "E2", and it doesn't get the Table name nor reference, but if I write the name…
Rodrigo Antonio Jimenez
  • 1
0
votes
0 answers

Why doesn't Excel show the full array when I type a structured reference into a cell?

I'm pretty new to Excel, so please forgive me. I'm watching a tutorial right now and when he types a structured reference into an empty cell, i.e. =Tracking[Category] it spits our the entire referenced array. When I do it, only the respective row…
Ferdi
  • 1
0
votes
0 answers

Replace the "@" for reference a row of a table with another "kind" of reference to that row

I want, if possible, to replace the "@" in the formula: =SUM(COUNTIFS(MyTable[@[a1]:[a6]],"B",Cond2,"S"), COUNTIFS(MyTable[@[a1]:[a6]],"A",Cond1,"<>0")) which it counts the cells of "MyTable" that fulfill some conditions that are found above…
mpolenis
  • 1
  • 2
0
votes
1 answer

Lock Structured References to column names in Excel-formulas when data Query is updated and refreshed

I use references to other columns in my table with the following formula: =TableName[@[ColumnHeaderName]] And use the following as a Table array: TableName[[#All];[ColumnHeaderName]] (this is used if I want to make a HLOOKUP to a specific cell given…
Emil Olesen
  • 1
  • 1
0
votes
1 answer

Excel Power Query and Cell refernces

I am looking to have some summary stats for a table that is generated by a query on another tab, but all the approaches I have taken fail for some reason. Using cell references -…
tormond
  • 412
  • 5
  • 16
0
votes
0 answers

Structure Reference for use in chart

I'm trying to create a function where I can identify the chart I intend to populate on Sheet1. The chart will get its x-values and y-values based on a structured reference. For…
Karsten Mikal Kopperstad
  • 1
1
2