Thread: Set cell to take value from most recently filled in column

Hi,

You can do this using the OFFSET function.

For example, where there is a list of data in column A starting in A1, use this

=OFFSET(A1,COUNTA(A:A)-1,0)

to return the last cell in the column.

I'm trying to figure out the offset function, and maybe I'm not understanding it correctly, but it seems that it just copies a cell and puts it in a different place?

I will give a more detailed example of what I am trying to do (the formatting is bad, but think of it as an excel looking file):
The info I have in my excel:

Totals for the year:
Program 1: 100
Program 2: 150
Program 3: 125

Amount Spent (What input would look like at the end of November)
October November December January
Program 1: 10 15
Program 2: 15 30
Program 3: 10 20

Amount Spent (What input would look like at the end of January)
October November December January
Program 1: 10 15 20 30
Program 2: 15 30 45 50
Program 3: 10 20 25 35

What I am want to figure out how to do:
(amount spent/total)
After November is input I want to see:
Program 1: 10% (10/100)
Program 2: 10% (15/150)
Program 3: 8% (10/125 )

After January is input I want to see:
Program 1: 30% (30/100)
Program 2: 33.3% (50/150)
Program 3: 28% (35/125 )

I know how to have it automatically calculate based on a cell, but I would like to be able to change the cell it is calculated from without having to manually input it.
EX. have it go from calculating C3/B10, C4/B11, C5/B12 to calculating E3/B10, E4/B11, E5/B12

I am thinking that it would know which column to use based on what is filled in. It would take the column furthest to the right that is filled in.

Is offset the right formula tool to do this?

See if the attached is what you intended.

The OFFSET function returns the contents of a cell offset by a number of rows and columns. By combining the offset function with the COUNT or COUNTA function it is possible to obtain the value in the final cell in a row or column, as per the example above.

I would suggest using LOOKUP given it is neither Volatile nor susceptible to blank data points, ie

=LOOKUP(9.99999999999999E+307,$E2:$P2)

would give you the last numeric value entered in range E2:P2

In short were it me I would adopt khamilton's earlier example.

Shouldn't the first lookup be referencing D31:O31 rather than D74:O74... given the latter is the divisor
(ie if row 31 returned 0 the subsequent division would generate #DIV/0! whereas if row 31 was not 0 and row 74 was zero the output would simply be 0% anyway)