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