How do you autofill cells containing table forumala nomenclature?

[PQK]

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]

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]

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.

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

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

 

[PQK]

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]

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!