How do you autofill cells containing table forumala nomenclature?

PQK

New Member
Joined
Sep 10, 2011
Messages
4
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: SUMIFS(datatable[Variable1],datatable[Variable2],C$3)

However, when I attempt to copy the formula down/across, the variables in brackets [] moves to the next variable in the datatable. Thus, the above example (if autofilled to next cell to the left) would become:

Ex: SUMIFS(datatable[Variable2],datatable[Variable3],D$3)

Is there a way to freeze the datatable variables when the formula is copied across similar to using the $ before a column letter or row number?

Any help would be greatly appreciated!!!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,717
Office Version
2010
Platform
Windows
Welcome to the board.

Have you tried holding the Ctrl key down as you drag?

If that doesn't work, try copying the formula rather than dragging it.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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: SUMIFS(datatable[Variable1],datatable[Variable2],C$3)

However, when I attempt to copy the formula down/across, the variables in brackets [] moves to the next variable in the datatable. Thus, the above example (if autofilled to next cell to the left) would become:

Ex: SUMIFS(datatable[Variable2],datatable[Variable3],D$3)

Is there a way to freeze the datatable variables when the formula is copied across similar to using the $ before a column letter or row number?

Any help would be greatly appreciated!!!
This is one of the reasons I absolutely hate structured referencing. :mad:

Since you only have a single criteria you can just use SUMIF.

=SUMIF(INDIRECT("datatable[Variable2]"),C$3,INDIRECT("datatable[Variable1]"))
 

PQK

New Member
Joined
Sep 10, 2011
Messages
4
Shg and T. Valko,

Thank you both for your replies!


I tried holding down the Ctrl key as a drag and, unfortunately, it didn’t work. Excel still changed the table variables in each subsequent cell that the structured reference formula was copied. I appreciate the tip however, and I will definitely keep it in mind for other applications.

Embedding the indirect function within my formula had the desired effect and I was able to click and drag the formula across multiple cells without worrying about Excel changing the values of my structured references. A bit cumbersome, but it works!

I find it perplexing that Microsoft didn’t have the forethought to create the same autofill functionality with its structured references that it uses in all other aspects of the program. At least an additional step like holding down the Ctrl key before autfilling would have been a great help. Sigh…

Well anyhow, thanks again for the responses!:)
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Shg and T. Valko,

Thank you both for your replies!

I tried holding down the Ctrl key as a drag and, unfortunately, it didn’t work. Excel still changed the table variables in each subsequent cell that the structured reference formula was copied. I appreciate the tip however, and I will definitely keep it in mind for other applications.

Embedding the indirect function within my formula had the desired effect and I was able to click and drag the formula across multiple cells without worrying about Excel changing the values of my structured references. A bit cumbersome, but it works!

I find it perplexing that Microsoft didn’t have the forethought to create the same autofill functionality with its structured references that it uses in all other aspects of the program. At least an additional step like holding down the Ctrl key before autfilling would have been a great help. Sigh…

Well anyhow, thanks again for the responses!:)
You're not the only one!

Thanks for the feedback! :cool:
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top