Formula needed: argument changes as more data is added
 

The math is simply the difference between the values in two cells, but one of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last column
is Prior Year. In At any given time, I want the value for the most recent
month minus the prior year. How do I get it to automatically pick up the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

Hi,

This will return the contents of the last populated cell in row 2

=LOOKUP(2,1/(2:2<""),2:2)

or to shorten the range

=LOOKUP(2,1/(A2:M2<""),A2:M2)

Mike

"BK" wrote:

The math is simply the difference between the values in two cells, but one of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last column
is Prior Year. In At any given time, I want the value for the most recent
month minus the prior year. How do I get it to automatically pick up the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

Hi,

Well we can only guess what result you expect from 'and so on' but there are
6 combinations of yes/no/na and one way is to put them in a 2 column table
like this and I used column L and M. Column L are the combinations and M are
what you want to return.

Col L Col M
yesyes aa
yesno bb
yesNA cc
nono dd
nona ee
nana ff

You can then use vlookup

=VLOOKUP(A2&B2,L1:M6,2,FALSE)

Mike

"BK" wrote:

The math is simply the difference between the values in two cells, but one of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last column
is Prior Year. In At any given time, I want the value for the most recent
month minus the prior year. How do I get it to automatically pick up the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

Try this:

=LOOKUP(1E100,1:1)

That will return the *last numeric value* from left to right in row 1,
A1:IV1.

--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
The math is simply the difference between the values in two cells, but one
of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last
column
is Prior Year. In At any given time, I want the value for the most recent
month minus the prior year. How do I get it to automatically pick up the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

OOPs

wrong thread

"Mike H" wrote:

Hi,

Well we can only guess what result you expect from 'and so on' but there are
6 combinations of yes/no/na and one way is to put them in a 2 column table
like this and I used column L and M. Column L are the combinations and M are
what you want to return.

Col L Col M
yesyes aa
yesno bb
yesNA cc
nono dd
nona ee
nana ff

You can then use vlookup

=VLOOKUP(A2&B2,L1:M6,2,FALSE)

Mike

"BK" wrote:

The math is simply the difference between the values in two cells, but one of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last column
is Prior Year. In At any given time, I want the value for the most recent
month minus the prior year. How do I get it to automatically pick up the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

Is it possible for it to look from right to left for the last numeric value?

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,1:1)

That will return the *last numeric value* from left to right in row 1,
A1:IV1.

--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
The math is simply the difference between the values in two cells, but one
of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last
column
is Prior Year. In At any given time, I want the value for the most recent
month minus the prior year. How do I get it to automatically pick up the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

Let's see what your idea of the *last* number is:

A1.....B1.....C1.....D1
..........10......22..........

Which do you consider to be the *last* number?

The formula I suggested will return 22 as the last number.


--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
Is it possible for it to look from right to left for the last numeric
value?

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,1:1)

That will return the *last numeric value* from left to right in row 1,
A1:IV1.

--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
The math is simply the difference between the values in two cells, but
one
of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last
column
is Prior Year. In At any given time, I want the value for the most
recent
month minus the prior year. How do I get it to automatically pick up
the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

Last number searching from right to left in H - D:

Column Contents of Cell

C Formula in question
D - G No values at this time (future dates)
H Last value entered


"T. Valko" wrote:

Let's see what your idea of the *last* number is:

A1.....B1.....C1.....D1
..........10......22..........

Which do you consider to be the *last* number?

The formula I suggested will return 22 as the last number.


--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
Is it possible for it to look from right to left for the last numeric
value?

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,1:1)

That will return the *last numeric value* from left to right in row 1,
A1:IV1.

--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
The math is simply the difference between the values in two cells, but
one
of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last
column
is Prior Year. In At any given time, I want the value for the most
recent
month minus the prior year. How do I get it to automatically pick up
the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

In other words, you want to find the *first* number from left to right:

=INDEX(D1:H1,MATCH(TRUE,INDEX(ISNUMBER(D1:H1),0),0 ))

--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
Last number searching from right to left in H - D:

Column Contents of Cell

C Formula in question
D - G No values at this time (future dates)
H Last value entered


"T. Valko" wrote:

Let's see what your idea of the *last* number is:

A1.....B1.....C1.....D1
..........10......22..........

Which do you consider to be the *last* number?

The formula I suggested will return 22 as the last number.


--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
Is it possible for it to look from right to left for the last numeric
value?

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,1:1)

That will return the *last numeric value* from left to right in row 1,
A1:IV1.

--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
The math is simply the difference between the values in two cells,
but
one
of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last
column
is Prior Year. In At any given time, I want the value for the most
recent
month minus the prior year. How do I get it to automatically pick
up
the
last non-blank cell in the row?

Thanks

Formula needed: argument changes as more data is added
 

Hi,

Try this array formula (Ctrl+Shift+Enter)

=INDEX(D1:H1,1,MATCH(TRUE,ISNUMBER(D1:H1),0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"BK" wrote in message
...
Last number searching from right to left in H - D:

Column Contents of Cell

C Formula in question
D - G No values at this time (future dates)
H Last value entered


"T. Valko" wrote:

Let's see what your idea of the *last* number is:

A1.....B1.....C1.....D1
..........10......22..........

Which do you consider to be the *last* number?

The formula I suggested will return 22 as the last number.


--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
Is it possible for it to look from right to left for the last numeric
value?

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,1:1)

That will return the *last numeric value* from left to right in row 1,
A1:IV1.

--
Biff
Microsoft Excel MVP


"BK" wrote in message
...
The math is simply the difference between the values in two cells,
but
one
of
the cells will change each time the table is updated.

For example, Row 1 headers are Apr, Mar, Feb, Jan, etc. and the last
column
is Prior Year. In At any given time, I want the value for the most
recent
month minus the prior year. How do I get it to automatically pick
up
the
last non-blank cell in the row?

Thanks