structured references

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...

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...

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...

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...

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"...

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...

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...

<!--[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...

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...

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...

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...

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]...

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...

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.

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...

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...

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...

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...

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...

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...