looking for information in the last column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hirsch - 16 Aug 2006 20:24 GMT

Imagine:
Jan Feb Mar Apri May June July Aug Sep Oct Nov Dec
part 1 2 2.5 2.5 3 3.25
part 2 1 1.1
part 3 6 7 34

I'm looking for a seperate column that will list out the last value entered
in a series of colums: hense (part 1) = 3.25 (part two) = 1.1 (part 3) = 34.

In my attempt to find a formula I thought of something like
=lastvalue(b3:b15) but that's not a formula.

Does anyone have any suggestions? As usual thanks in advance.

Reply to this Message

David Billigmeier - 16 Aug 2006 20:36 GMT

The following formula assumes your table is in the upper most left hand part
of your spreadsheet... e.g. part 1,2,3 ended up in A2,A3,A4 and your month
names are in B1, C1, D1, etc.

=VLOOKUP("part1",$A$2:$I$4,MAX(IF(A2:I2<>"",COLUMN(A2:I2),"")),0)

Signature

Regards,
Dave

> Imagine:
> Jan Feb Mar Apri May June July Aug Sep Oct Nov Dec
[quoted text clipped - 9 lines]
>
> Does anyone have any suggestions? As usual thanks in advance.

Reply to this Message

Hirsch - 16 Aug 2006 20:46 GMT

David I appreciate the attempt, I should have stated that the numbers can go
up or down as they goto the left.

thank you anyhow.

> The following formula assumes your table is in the upper most left hand part
> of your spreadsheet... e.g. part 1,2,3 ended up in A2,A3,A4 and your month
[quoted text clipped - 15 lines]
> >
> > Does anyone have any suggestions? As usual thanks in advance.

Reply to this Message

David Billigmeier - 16 Aug 2006 20:56 GMT

I'm not sure what you mean. The formula I gave will output the exact
examples you posted in your table (e.g. "part1" will output 3.25, "part2"
will output 1.1 and "part3" will output 34).

Are you getting confused because I hardcoded "part1" into the formula? If
so create a helper cell (e.g. J1) in which you place your part number, and
use this formula:

=VLOOKUP(J1,$A$2:$I$4,MAX(IF(A2:I2<>"",COLUMN(A2:I2),"")),0)

I also should have mentioned this is an array formula so you need to commit
it with CTRL+SHIFT+ENTER.

If this still isn't what you mean post another example specifying better.

Signature

Regards,
Dave

> David I appreciate the attempt, I should have stated that the numbers can go
> up or down as they goto the left.
[quoted text clipped - 20 lines]
> > >
> > > Does anyone have any suggestions? As usual thanks in advance.

Reply to this Message

Toppers - 16 Aug 2006 20:59 GMT

David's formula will work as it looks at the maximum COLUMN number which is
non -blank. And it should be entered with Ctrl+Shift+Enter

Reply to this Message