formulae to copy cells from 1 worksheet and paste into another?

[MikeR-Oz]

I have rows of data and coluims and would like to copy from 1 format to
another on a diffrent sheet via a formula to minimise the amount of copying
and pasting i'am doing . Is this possible?

Current format is Worksheets that are by day of week and within each day of
week I have a column of store names and then the next columns are in wee date
order and contain the sales for each store running down ther page for the
week. next sheet is the next week day and so forth.

I want to now have the days of the week in 1 column and the weeks across the
top in the other columns with the sales for a single store running across the
rows in a new worksheet.
FROM THIS:-

Monday WorkSheet

Cloumn _A Cloumn _B Column_C
Store Name Week 1 Week 2

Store X
Store B
Store J

TO NOW THIS

Store A only

Column_A Column_B Column_C
Row1 Weekday Week 1 Week 2 etc etc

Row2 Monday
Row3 Tuesday
Wednesday
etc
etc

I am looking for trends and patterns

Thanks
Mike

[Clivey_UK]

Mike,
Try this:
1. You need to use the Store Name in the formulas, and this can come from the Sheet Name (e.g. 'Store A') by using the formula
=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
this basically takes the path/file/sheet information, and returns just the sheet name from it. Put this formula in say cell A20.

2. In the sheet called Store A, put this in B2 (i.e. against Monday for Week 1):
=VLOOKUP($A$20,Monday!$A$2:$Z$100,COLUMN(B1))
This first finds out what store you're interested in ('Store A' as this is the contents of cell A20).
Then it looks up 'Store A' in Monday's sheet and returns the result for that week number.

Now copy the formula across the weeks, and down the days. Unfortunately you'll have to change the day shown in the formulas in each of the 7 different rows for Monday to Sunday. Do this by selecting all the formulas in that row and doing a Find/Replace, e.g. replacing Monday for Tuesday. That way you'll only have 6 changes to make.
There is probably a way of getting the formula to take the day from column A but I'm not sure how to do that at the moment.

Note that for this to work, you have to have the meet the following conditions:
a) The stores must be shown in alphabetical order (because of the Vlookup)
b) The week numbers must be in the same columns in all sheets (e.g. column B is always Week 1)
c) I've assumed that the range of data in the day sheets is A2 to Z100 to make the formula in point 2 above. Change the formula if the range is different.

Let me know if you need more information/explanation.
Clive

[MikeR-Oz]

Thanks But not working Clivey_UK,
YOU wrote " In the sheet called Store A, put this in B2 (i.e. against Monday
for
Week 1):
=VLOOKUP($A$20,Monday!$A$2:$Z$100,COLUMN(B1))
This first finds out what store you're interested in ('Store A' as this
is the contents of cell A20).
Then it looks up 'Store A' in Monday's sheet and returns the result for
that week number."

to confirm:-

WHY A20? SHOULD IT BE A2 WHICH IS THE DATA IN THE MONDAY SHEET AND IS THE
NAME -STORE A.

OR USE A3 WHICH IS THE NEXT STORE NAME - STORE B, etc etc

then:-

A$2:$Z$100 is the range in the sheet 'monday' and is the data including the
store names and the columns to z that include all the weeks data, for mondays
over the 5 months

and then:-
COLUMN(B1))
is where to start placing the 'looked up' information for A2

Correct?

Then why is it not working? I just get a 0

Mike




"Clivey_UK" wrote:

>
> Mike,
> Try this:
> 1. You need to use the Store Name in the formulas, and this can come
> from the Sheet Name (e.g. 'Store A') by using the formula
> =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
> this basically takes the path/file/sheet information, and returns just
> the sheet name from it. Put this formula in say cell A20.
>
> 2. In the sheet called Store A, put this in B2 (i.e. against Monday for
> Week 1):
> =VLOOKUP($A$20,Monday!$A$2:$Z$100,COLUMN(B1))
> This first finds out what store you're interested in ('Store A' as this
> is the contents of cell A20).
> Then it looks up 'Store A' in Monday's sheet and returns the result for
> that week number.
>
> Now copy the formula across the weeks, and down the days. Unfortunately
> you'll have to change the day shown in the formulas in each of the 7
> different rows for Monday to Sunday. Do this by selecting all the
> formulas in that row and doing a Find/Replace, e.g. replacing Monday
> for Tuesday. That way you'll only have 6 changes to make.
> There is probably a way of getting the formula to take the day from
> column A but I'm not sure how to do that at the moment.
>
> Note that for this to work, you have to have the meet the following
> conditions:
> a) The stores must be shown in alphabetical order (because of the
> Vlookup)
> b) The week numbers must be in the same columns in all sheets (e.g.
> column B is always Week 1)
> c) I've assumed that the range of data in the day sheets is A2 to Z100
> to make the formula in point 2 above. Change the formula if the range
> is different.
>
> Let me know if you need more information/explanation.
> Clive
>
>
> --
> Clivey_UK
> ------------------------------------------------------------------------
> Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
> View this thread: http://www.excelforum.com/showthread...hreadid=524402
>
>

[Clivey_UK]

Mike,
See point 1. re what to put in A20. The Vlookup refers to A20 because it is looking up the Sheet name (e.g StoreA).
I think the easiest way to explain this (as I can't attach my example Excel file) is to attach a jpg of it. You can see the formula for selected cell B2, and the result. The two boxes below show what I've got in Monday's and Tuesday's sheet, so you can see the result being returned comes from these values.
Hope this answers your questions.
Clive

Thanks But not working Clivey_UK,
WHY A20? SHOULD IT BE A2 WHICH IS THE DATA IN THE MONDAY SHEET AND IS THE
NAME -STORE A.

OR USE A3 WHICH IS THE NEXT STORE NAME - STORE B, etc etc

then:-

A$2:$Z$100 is the range in the sheet 'monday' and is the data including the
store names and the columns to z that include all the weeks data, for mondays
over the 5 months

and then:-
COLUMN(B1))
is where to start placing the 'looked up' information for A2

Correct?

Then why is it not working? I just get a 0

Mike

>[/color]

[MikeR-Oz]

I could not see the attachment? Do I need to look somewhere ooin particular?
Mike

"Clivey_UK" wrote:

>
> Mike,
> See point 1. re what to put in A20. The Vlookup refers to A20 because
> it is looking up the Sheet name (e.g StoreA).
> I think the easiest way to explain this (as I can't attach my example
> Excel file) is to attach a jpg of it. You can see the formula for
> selected cell B2, and the result. The two boxes below show what I've
> got in Monday's and Tuesday's sheet, so you can see the result being
> returned comes from these values.
> Hope this answers your questions.
> Clive
>
> MikeR-Oz Wrote:
> > Thanks But not working Clivey_UK,
> > WHY A20? SHOULD IT BE A2 WHICH IS THE DATA IN THE MONDAY SHEET AND IS
> > THE
> > NAME -STORE A.
> >
> > OR USE A3 WHICH IS THE NEXT STORE NAME - STORE B, etc etc
> >
> > then:-
> >
> > A$2:$Z$100 is the range in the sheet 'monday' and is the data including
> > the
> > store names and the columns to z that include all the weeks data, for
> > mondays
> > over the 5 months
> >
> > and then:-
> > COLUMN(B1))
> > is where to start placing the 'looked up' information for A2
> >
> > Correct?
> >
> > Then why is it not working? I just get a 0
> >
> > Mike
> >
> > >
>
>
> +-------------------------------------------------------------------+
> |Filename: mike example.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=4502 |
> +-------------------------------------------------------------------+
>
> --
> Clivey_UK
> ------------------------------------------------------------------------
> Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
> View this thread: http://www.excelforum.com/showthread...hreadid=524402
>
>[/color]

[Clivey_UK]

Mike,
Look just above where you wrote 'I could not see the attachment'. It's a hyperlink to Mike Example.jpg. Or do Ctrl F and find 'Mike Example.jpg' on the page.
Clive

I could not see the attachment? Do I need to look somewhere ooin particular?
Mike

[MikeR-Oz]

I still cannot get any attachment my end - I do not want to waste your time -
I will have a nother look at your formula and try and step my way througha
gain - sorry mate for wasting your time.
Mike

"Clivey_UK" wrote:

>
> Mike,
> Look just above where you wrote 'I could not see the attachment'. It's
> a hyperlink to Mike Example.jpg. Or do Ctrl F and find 'Mike
> Example.jpg' on the page.
> Clive
> MikeR-Oz Wrote:
> > I could not see the attachment? Do I need to look somewhere ooin
> > particular?
> > Mike
> >
>
>
> --
> Clivey_UK
> ------------------------------------------------------------------------
> Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
> View this thread: http://www.excelforum.com/showthread...hreadid=524402
>
>

[Clivey_UK]

Mike,
Clicking the link takes you to http://www.excelforum.com/attachment...2&d=1143027236 so just use this link instead.
Clive

I still cannot get any attachment my end - I do not want to waste your time -
I will have a nother look at your formula and try and step my way througha
gain - sorry mate for wasting your time.
Mike

[MikeR-Oz]

Thanks Clive, that link worked - will now go back and check out here I went
wrong. Cheers
Mike

"Clivey_UK" wrote:

>
> Mike,
> Clicking the link takes you to
> http://www.excelforum.com/attachment...2&d=1143027236
> so just use this link instead.
> Clive
>
> MikeR-Oz Wrote:
> > I still cannot get any attachment my end - I do not want to waste your
> > time -
> > I will have a nother look at your formula and try and step my way
> > througha
> > gain - sorry mate for wasting your time.
> > Mike
> >
> >
>
>
> --
> Clivey_UK
> ------------------------------------------------------------------------
> Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
> View this thread: http://www.excelforum.com/showthread...hreadid=524402
>
>