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