lookup?

[Ray]

Hello,
Hoping someone can help. I have a sheet with names down
column A. Each week I add values in each subsequent
column which has headings of "Week 1", "Week 2" etc, up
to "week 22". What I need to do on another sheet is add a
formula that finds the last column in the original sheet
with values in it and inserts that value against the same
name. When new values are added to the next column on the
original sheet for the next week these will replace the
previous weeks value in the 2nd sheet. Sorry if it sounds
confusing.

Thanks for any help.

Ray

 

[Frank Kabel]

Hi Ray
try the following formula
=OFFSET('sheet1'!$B$2,0,COUNTA($B$2:$X$2)-1)

 

[Ray]

Thanks for the formula, but it doesn't quite do what I'm
after.
Is there a formula I can replace the column number ("6"
in the example below) that returns the required text in
VLOOKUP to say move to the first blank cell then come
back one and return the text/value in that column.

=VLOOKUP(B5,Summary!$A$4:$M$32,6,FALSE)

Thanks
Ray

 

[Frank Kabel]

Hi
not fully tested but try the following formula
=OFFSET('Summary'!$B$4,MATCH(B5,'Summary'!$A$4:$A$32,0)-
1,COUNTA(OFFSET('Summary'!$B$4:$M$4,MATCH(B5,'Summary'!
$A$4:$A$32,0)-1,0))-1)