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 com
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 jus
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 fo
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 thi
is the contents of cell A20).
Then it looks up 'Store A' in Monday's sheet and returns the result fo
that week number.

Now copy the formula across the weeks, and down the days. Unfortunatel
you'll have to change the day shown in the formulas in each of the
different rows for Monday to Sunday. Do this by selecting all th
formulas in that row and doing a Find/Replace, e.g. replacing Monda
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 fro
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 followin
conditions:
a) The stores must be shown in alphabetical order (because of th
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 Z10
to make the formula in point 2 above. Change the formula if the rang
is different.

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

 

[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

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]

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

[/QUOTE]


+-------------------------------------------------------------------+
|Filename: mike example.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4502 |
+-------------------------------------------------------------------+

 

[MikeR-Oz]

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

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

+-------------------------------------------------------------------+
|Filename: mike example.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4502 |
+-------------------------------------------------------------------+
[/QUOTE]

 

[Clivey_UK]

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

 

[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]

Mike,
Clicking the link takes you t
http://www.excelforum.com/attachment.php?attachmentid=4502&d=114302723
so just use this link instead.
Clive

 

[MikeR-Oz]

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