Selecting the last figure in a row

[DangerMouse]

Hi all,

I cant seem to configure my offset function correctly to select the
last figure in a row. I'm attempting to use the COUNT function to
generate the correct cell reference dynamically but it doesnt appear to
be working.

Any help appreciated.

DM

 

[broro183]

Hi Dangermouse,

Try using Counta instead (check out the Help files for a comparison) &
if that doesn't help, post back & include some more detail eg your
formula as it is currently, the layout of your sheet etc.

"I cant seem to configure my offset function correctly to select the
last figure in a row."
Does mean you have a rows with data in a varying # of columns & you
want to retrieve the value in the last column occupied on each row (eg
column F or column Z)?
Or are you trying to get the value in the last row (eg from row 100)?

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[DangerMouse]

Thanks for the reply Broro.

My current attempt is actually quite embarressing lol, I've attempted
modifying a dynamic range formula that was used to select all data in a
number of rows, listing down.

I attempted COUNTA which I believe just takes into account text
strings, but to no avail.

My layout is actually very simply, however the problem is with the data
that goes into it, hence the need for this method.

The layout is a simple table; Catagories in column A, Months from
Column B across e.g.

-----Jan---Feb---March
--x--
--y--
--z--

For each x, y z I wish to select the latest figure filled in. Although
I've read that this can be done using months, I would prefer to keep
the formula flexible and avoid data time functions.

Hope this makes sense.

Thanks again for getting back to me.

DM

 

[Ardus Petus]

=INDEX($B2:$M2,,COUNTA($B2:$M2))

HTH
--
AP

"DangerMouse" <[email protected]> a
écrit dans le message de
news[email protected]...

 

[broro183]

Hi,
Hey, we all start somewhere...
Thanks, that makes it much clearer

The following link uses months & is flexible but shows how to get a
cumulative result rather than just how "to select the latest figure
filled in". Have a look - it may give you some other ideas...
http://excelforum.com/showthread.php?t=503228

Anyway,
To select the latest figure try entering this in column N (I used row 4
as my first data row & by my test this is the column after Dec) and
copying it down for rows x,y,z etc:
=OFFSET(A4,0,COUNTA(B4:M4),1,1)

or, if all your figures are values, the following should work:
=OFFSET(A5,0,COUNT(B5:M5),1,1)

Of course, this will only work if there are no columns with blanks in
them which are before the latest figure. This could probably be
overcome by using SumProduct, but you will need someone else to help
out with that - I'm still learning about it & am off to bed now.
Can anyone provide a sumproduct solution?

Goodluck, if you have no luck with this, post the "dynamic range
formula" which you're working on & I'll have a look after work
tomorrow.


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[DangerMouse]

Thanks thats great!

DM

 

[broro183]

Hi Dangermouse,
Pleased we could help.

Ardus, I like your solution, I don't know which (offset or index) woul
be quicker over a large range but index seems "neater" to look at.

Rob Brockett
NZ
Always learning & the best way to learn is to experience..