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

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

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

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

[/color]


+-------------------------------------------------------------------+
|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

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]

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

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

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

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