last column

[kevcar40]

Hi
I have a table of data starting at week 1(b) and going to week 52(Ab)
what i would like to do is find the last week (using week number)
copy only that column of data to be pasted else where

any ideas

thanks

kevin

 

[DILipandey]

Hi,

supposing you have the weeks starting from column B, use following formula
in column A and drag it down.

=OFFSET(B2,0,MATCH("*52*",$1:$1,0)-2)

What it will do, it will go ahead and pickup the week 52 data.
You might need to change the week in the formula as per your preference.

Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[kevcar40]

Hi,

supposing you have the weeks starting from column B, use following formula
in column A and drag it down.

=OFFSET(B2,0,MATCH("*52*",$1:$1,0)-2)

What it will do, it will go ahead and pickup the week 52 data.  
You might need to change the week in the formula as per your preference.

Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India






- Show quoted text -

Thanks for reply
that works fine
is there away where we can read the current week with having to
manually change the formula

 

[DILipandey]

Yes, there is a solution. Try following:-
=OFFSET(B2,0,MATCH(weeknum(NOW(),1),1:1,0)-2)

But to get this function, you need to put week numbers only like..1,2,3 etc
in columns starting column B instead of week 1, week 2 etc.
Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[kevcar40]

Yes, there is a solution.  Try following:-
=OFFSET(B2,0,MATCH(weeknum(NOW(),1),1:1,0)-2)

But to get this function, you need to put week numbers only like..1,2,3 etc
in columns starting column B instead of week 1, week 2 etc.
Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India






- Show quoted text -

thank you very much i will try it now

 

[kevcar40]

Yes, there is a solution.  Try following:-
=OFFSET(B2,0,MATCH(weeknum(NOW(),1),1:1,0)-2)

But to get this function, you need to put week numbers only like..1,2,3 etc
in columns starting column B instead of week 1, week 2 etc.
Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India






- Show quoted text -

i have tried this formula but i am getting N/A as a result

here is what i have done
i inserted a column (moving table over)
i put the formula in column A

=OFFSET(C145,0,MATCH(WEEKNUM(NOW(),1),1:1,0)-2)
Column b contains the faults
column c is week 1 which is now 1

thanks


kevin