One of the most useful features of Excel tables are structured references. When you have just stumbled upon a special syntax for referencing tables, it may look boring and confusing, but after experimenting a bit you will surely see how useful and cool this feature is. Continue reading
Comments on: Structured reference in Excel tables
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 54
I'm having an issue. I'm using Table structured references in my linear regression sheets. However, after I have everything setup like I want it and I save and re-open the file, the structured reference formulas turn into regular formulas referencing the rows/columns of the current data in the sheet like a regular formula. I need my structured references to stay intact so they will grow and shrink as I add and remove data to the tables.
for example:
=MEDIAN((Table14[CostPerUnit]))
after saving and re-opening turns into:
=MEDIAN('Sheet1'!$K$16:$K$69)
Normal behavior? Anyway to avoid it or am I missing something? Any help is appreciated!! Thank you!
I have the same problem. Any solution?
I see your examples on structured references in columns. How can I use this in rows? I want a cumulative total column next to a column of values. I can do this with =SUM($B$3:B6) in column C but can I do this with structured references? My table has a column called DESCRIPTION and one called AMOUNT. I would like a cumulative column with a running total.
Thanks for the help
Sergio
Hi Sergio, similar to my answer above you can use the Header to 'fix' the top. Try:
=SUM([[#Headers],[Column B Name]]:[@[Column B Name]])
Hi
I'm trying to calulate the sequential duplicate number in a list of Id Numbers in a table. There are different numbers of duplicates for each Id number. Because the table is downloaded as an excel template from Dynamics I need to use the structured reference (using cell references doesn't calculate in the downloaded spreadsheet as the formula doesn't copy down without structured references). Normally I would just use =countif(d$2:d2,d2), but this doesn't automatically calculate when downloaded from Dynamics. When I create my version of this in a structured reference formula it doesn't work. I've tried =COUNTIF([Form '#]:[@[Form '#]],[@[Form '# ]]) where Form # is the field in the table but it proviudes me with the number of duplicate records in every row. I've tried a few alternate versions but they don't work either. Thanks in advance, Shane
Shane, I was looking something else up and saw your comment. Sorry I didn't see it earlier. If you are still wondering the problem is that the first [Form '#] of your "range" is already the entire column and hence why all results are total number of that duplicates. Try [[#Headers],[Form '#]] instead to force it to use the header cell of that column for your range. So the new formula would be:
=COUNTIF([[#Headers],[Form '#]]:[@[Form '#]],[@[Form '# ]])
Hi Matt, I was having the same exact issue as Shane with the table reference for a COUNTIF formula. I tried your table reference fix and I didn't get it entered right, the first time. I tried it again, a second time and sure enough, it worked. I've got to say, YOU ARE THE MAN!!! No one out there on the internet explains, or shows how to do a mixed reference in a table reference version like that. Thanks for showing how it's done.
I've discovered structured references and want to do more with them. Seems like they only work as intended (auto filling columns) on the same worksheet with the table / query. Is there a trick to doing this from a different worksheet?
Hi Dave,
Structured references can also be used from a different worksheet. When making a formula, you just need to select cells in the original table using the mouse. In this tutorial, you can find some examples of formula with structured references outside a table.