Array formula that uses last written cell

**dcguerra** · 11-01-2013, 12:23 PM

Hi,

I wanted to know how can i make an array formula that uses the last written cell to do a calculation.

Every day I upload new information in a table. And the last column of this table makes the diference between the last updated data and the year-end figures.

Let's say:
Monday: i put the values on cell A1 this means than the last cell (AB1) will be doing A1-AB1
Tuesday: i put the values on cell B1 this means than the last cell (AB1) will be doing B1-AB1
Wednesday: C1 - AB1
Thursday: D1 - AB1
and so on...

Normally i do this manually but i guess there is probably an automatic way to do this.

Can someone help me?

Thanks in advance.

**dipique** · 11-01-2013, 12:59 PM

Formulae are not aware of when data was last updated. You will need to either use VBA or include "Date Updated" information that formulae can reference.

**dcguerra** · 11-01-2013, 01:05 PM

Thanks.

Can you help me do either of the options you mentioned?

**sktneer** · 11-01-2013, 03:16 PM

You can use dynamic named ranges and refer them in your array formulas. You can define dynamic named ranges by using the following formula.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

In this case you are creating a dynamic named range in column A where A1 contains the header and the values start from A2.