Cell Reference in a table that is changing

[dw_22801]

Hi,

I am trying to reference a cell that is within a table, but it needs to always be the last cell in a specific column, and as I add rows, the cell that holds the data that I need moves down with each row, obviously. Not sure how to make the reference stay with whichever cell has that data. The data is a running balance of inventory, where the last row is always the current inventory balance after all transactions, which is the data I need to reference. Any ideas?

Thanks!

[jaslake]

Hi dw

How/where are you referencing that Cell? Is it a "True" Excel Table?

[Aardigspook]

Would a Totals row work for you? If you click in the Table, then look on the Table Tools | Design tab, there's a check-box for 'Total Row'. When you click this, a row is added at the bottom of the table and you can choose from different functions for each column from a drop-down list*.

Once you have the Total row, it can be referred to using a structured reference like this:
=Table1[[#Totals],[Inventory additions]]

As you add more rows, the Total row always stays at the bottom.

* None (i.e. no function), Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var are immediately accessible - other functions can be chosen using the 'More Functions...' at the bottom of the list.

Does that help?


Edit: I've assumed you're using an Excel Table. If not, then try this, if there are no blanks in the column you have your inventory balance in - change all three 'G's to the correct column:

Please Login or Register  to view this content.