24 questions
1
vote
0
answers
129
views
How to set ListObject (Excel table) formulas with structured references without error 1004
I’m trying to insert formulas programmatically into an Excel table (ListObject) using VBA.
The formulas use structured references (e.g. [@ID], Table_Lookup[Column]) rather than standard A1 ranges.
...
1
vote
0
answers
284
views
Using structured references, how do I link two Excel tables with different starting rows?
All of the examples I've seen of referencing one table data in another, e.g.
=table1[@Table1Column]
assume that the tables start on the same row.
In my case, I have table1 that is starting on row 5, ...
- 1,795
1
vote
2
answers
734
views
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 ...
0
votes
0
answers
67
views
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 ...
0
votes
0
answers
74
views
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 ...
- 1
2
votes
1
answer
1k
views
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 ...
- 21
2
votes
1
answer
361
views
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 ...
- 23
0
votes
1
answer
3k
views
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 ...
0
votes
1
answer
171
views
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 - COUNTIF(INDEX(Errors!...
- 452
0
votes
0
answers
311
views
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 example:
addTrend("...
0
votes
0
answers
205
views
VBA updating R1C1 formula to table formula
I just started with a new company and checking their VBA codes and I noticed that they haven't been using table references and were using R1C1 references.
Is there a way for Excel to update ...
- 11
0
votes
1
answer
771
views
Excel Structured Reference - Retrieving current row with @ or #This Row is not working
I am on office 0365 enterprise version - Microsoft® Excel® for Microsoft 365 MSO (16.0.14228.20292) 32-bit. I am working on a table and would like to use structured referencing to dynamically retrieve ...
0
votes
0
answers
50
views
Structured table formula to separate text string after a hyphen
I am trying to find a oneliner VBA code that will use structured table reference to edit values in a column given as "United States of Amercia - USA". The aim is to delete everything before ...
- 21
0
votes
0
answers
48
views
Excel changes a formular I define with the Range.FormulaLocal into a Formula with structured references. I do not want that, how can I change it? [duplicate]
I want to insert a formula into a sequence of cells by using a "for-loop". My formula references other cells that change for each loop. The formula itself works but when I enter it with ...
user13539707
2
votes
1
answer
1k
views
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[&...
- 676
0
votes
0
answers
89
views
Use the text of a cell as the argument of range() with structured references
I want to execute an advanced filter with vba. The problematic step is that the name of the source table (for the advanced filter) is written in a cell and it seems I can't find a way to pass it to ...
0
votes
2
answers
241
views
Conditional copying from a table in Excel
I'm trying to copy to both debit/credit columns to other tables which match only the respective account value i.e. all Cash entries go to a Cash Account table, etc. I'll also need a way to omit those ...
- 181
2
votes
1
answer
129
views
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 ...
- 27
1
vote
1
answer
245
views
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 ...
- 61
0
votes
1
answer
644
views
How to reference a table ignoring the offset
Excel 2013: I have several single-column tables on a Lookup's sheet which are used to validate various columns around my workbook.
I now need to add a mapping table to map all values from one of ...
- 1,975
20
votes
1
answer
16k
views
$ (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 ...
- 2,720
2
votes
2
answers
125
views
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 ...
- 23
0
votes
1
answer
136
views
Excel table reference (structured) change to absolute range (A1) after re-opening
I have a Table named table2 on my sheet2, and I'm using its values on sheet1 with the help of structured references, like:
=average(table2[values])
But after saving and re-opening the file, all my ...
- 4,122
1
vote
1
answer
945
views
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 ...
- 62k