2

The Table format (Home> Format As Table) option is a good feature of Excel. I am using it in Excel 2013.

But when I put a formula and try to copy it across, it does not work. Trouble comes when you want to repeat different sets of formulas etc.

Does anybody know a solution to this. I know that by dragging the formula, it works, but when you have thousands of rows, its terrible to drag.

Edit 1: Putting manual referencing also works, but its cumbersome, and we don't use excel for doing it manually.

Edit 2: A sample file uploaded. Link. In the file I have data in Table format. I put a Subtotal formula in B1, and copied it to C1 and D1. The references do not change in C1 and D1.

Improve this question
4
  • This question is impossible to even speculate on without sample data and sample formula(s) together with expected results.
    – user385793
    Dec 28, 2014 at 23:04
  • Expected result is simple, when u copy a formula, the reference should change automatically, as is the simple way excel works..
    – Firee
    Dec 29, 2014 at 6:00
  • If you put a Total Row to use, there should be no problem. If you are not using a Total Row then some sample data with the formula located appropriately would shed some light on your problem.
    – user385793
    Dec 29, 2014 at 6:10
  • Am not using a Total Row. Sample file uploaded.
    – Firee
    Dec 29, 2014 at 7:13

1 Answer 1

Reset to default
0

The structured references you are trying to use are treated like named references when used outside of the actual table. That is to say, using Table1[USA Sales] rather than B$3:B$6 is like defining B$3:B$6 using Formulas ► Defined Names ► Name manager; there is an underlying cell reference but it is locked. Conversely, the Table1 area can be used like a defined name are sliced off with INDEX.

       Structured References in Tables

The formula in D2 is,

=SUBTOTAL(9,INDEX(Table1, , COLUMN(B:B)))

Fill right as necessary. That formula will dynamically grow with the addition of new rows to Table1.

Using a Total Row does not require this INDEX slicing as the total row is a part of Table1. Filling right in a total row would adjust to the column above the new formula much as relative cell references do. You are running into difficulties because you are using the structured references from outside the table.

Using structured references with Excel tables

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .