structured references

  1. szita2000

    Index Match Match from a named table to a range

    Hi Lads. New year new challenge. TLDR: Converted range to a named table and all Index Match Match formulas stopped working from the new table Back story: I need to make queries to numerous excel file in a folder, where we did put some tables (Not formatted as tables) but the same structure...
  2. B

    How do I prevent Structured References from shifting when adding a new column to table imported via Power Query?

    I have a data table that I query from one shared department spreadsheet. I pull that data into my spreadsheet with Power Query, I don't perform any special steps, just import. I then reference that DATA[Wire 1] and DATA[Wire 2], for example. If I put a column [Wire 1.5] in between [Wire 1] and...
  3. D

    Conditionally format Table columns based on the Column Header value (using structured references)

    Hi there, I've got a Table (let's call it Table1) with a number of 'spacer' columns in it. Those spacer columns each have the word "spacer" somewhere in their column headers. For example, a header might be "Spacer Column 3" or "SPACER66" and so on. I want to conditionally format all the...
  4. J

    YEARFRAC doesn't work with structured reference in excel table

    Using Excel 2016. Create an excel table with 3 columns: date1 date2 year_frac Add one row to the table. In the date columns, put any dates Format the table using the "Format as Table" feature. in the year_frac column enter this formula: =YEARFRAC([date1],[date2]) The formula appears to work...
  5. G

    Using a value in a table to set a "max length" conditional formatting rule in a cell on another sheet.

    Hi, I'm new to 'the deeper excel' (beyond pivot tables and vlookup) in the last two months as I work on a side project for work, and finally throwing in the towel and creating an account here to ask a question. For the purposes of this question, I have a worksheet titled "Customer Entry"...
  6. P

    Count the volume of Unique Results based on a condition (using structured references)

    Hi, I have a list of Account User IDs (Column 1) and a list of email addresses(column 2) associated with that user. I need to count the number of unique email addresses per user and display this on each row (column 3). The data I have is stored in tables so I need the formula to allow for...
  7. K

    Use structured references from separate workbooks?

    Hello, I'm working on simplifying the process of consolidating sales data. Each division (let's say A,B, and C) sells products through dealers. All of the divisions sell through the same dealers, but the dealers are always changing monthly (being added or removed). Each month I send out...
  8. P

    Problem using indirect on structured table in array formula

    <!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]-->PROBLEM USING INDIRECT ON STRUCTURED TABLE IN ARRAY FORMULA I use a template to extract data from an external source into a structured table. The name of the table is based...
  9. E

    How do I populate unique cells from one worksheet to another based on a reference field?

    Hi all, I'm trying to come up with a formula that will populate unique cells in a new worksheet by referencing another worksheet that contains all the data. So far I've used an INDEX MATCH formula, but that takes across all cells and displays the contents of the desired ones, while producing...
  10. J

    Structured reference and offset

    I am using offset and structured refs in a table to create dynamic arrays. =OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1) My intention with this formula is to create an array that grows in height for each row. But the return value is just a single...
  11. W

    Structured reference that refers to table name,a value in header row, and a value from 1st column

    So, I have multiple tables that each represent a fiscal year. (TableFYE_2010, TableFYE_2011, TableFYE_2012, etc.,) and the tables are all set up with the first column header for GL code the Column name is GLCode, and the remaining column headers are department codes (various three digit...
  12. P

    Structured Referencing of a portion of a colum

    What is the syntax to reference a table's column, not the whole column but the portion from row 1 to the current row? Let's say we are in the 5th row of a table, in column "MyColumn" Equivalent to 6th row and 2nd column (column B) in the spreadsheet Referencing the cell will be [@MyColumn]...
  13. P

    Using Structured References and index,row functions

    Hi There I have a formula that is doing exactly what I want when on a set table on the same sheet where I can manually define the arrays... see below Sample Original Data: Table3 <tbody> A43 B C D E F G H I J K L M 44 45 Month Type Candidate Client TOTAL...
  14. D

    Refer to Table column by position / index?

    I know I can easily refer to a table column by name, as in Table1[column name] But is there a way to refer to a table column by it's position? For example, refer to the first column, third column, etc. without using the column name? Hoping there is some way to do this using structured references.
  15. A

    Dynamically reference into a structured reference column header in a table

    Hi Hopefully someone can help here as I have drawn a complete blank! I have a table with dates as headers. I want to sumif rows where the sum_range is dynamically referenced. So this formula works... =SUMIF(Rev_WK12[[#All],[ITEM]],A8,Rev_WK12[[#All],[30/03/2013]]) table name = Rev_WK12 A8...
  16. E

    Locking part of a formula with structured references

    This may not be possible but it has bothered me for quite some time. I'm looking for a way to lock part of a formula with multiple structured references. I'm aware that when using structured referencing that filling or dragging will shift all of the references and that copying and pasting will...
  17. P

    How do you autofill cells containing table forumala nomenclature?

    Dear Forum, I recently created a simple dashboard that counts and sums various information from an excel 2007 data table. I can easily pick data from the table using table formula nomenclature (I think it is also called structured references) Ex...
  18. S

    Indirect function with table formula

    I am using the table function and structured reference for a formula on the far right marked in yellow, "YTD Quota". That formula is: =SUM(Main[@[Jan]:[Mar]]), and sums Jan, Feb and Mar (also in yellow) in the row "Name 1". I would like the second sum term in the formula "Mar" to be...
  19. P

    structured references in series formula?

    Hello. I've just tried to use a structured reference (eg sheetname!tablename[columnname]) inside a SERIES formula without luck, the instant I press enter excel replaces the structured reference with an absolute reference (eg sheetname!$a$1:$a$10). Trying to use INDIRECT doesn't help either, as...
  20. S

    2007 Structured Reference auto-complete?

    I’m trying to use “auto-complete” to learn more about Structured References to Tables in Excel 2007 (Windows XP SP3 if that matters). A couple of tutorial articles on the web appear to say that if I: - Select a cell. (I’ve tried this with cells that are and are not in aTable.) - Type “=” in...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back