last column

K

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
 
D

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
 
K

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
 
D

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
 
K

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top