New to excel not sure how to even word what im trying to work out

[tommyukuk]

Hi, first post so please bear with me. We have a spreadsheet we use at work that im trying to play around with to try make a little easier to replicate for our other stores to use aswel.
it was originally done in apple numbers but trying to get it to work in excel.
We have 1 sheet that contains 52 seperate tabs? for each week of the year as below:

We manually fill in the data each day and the bits at the bottom populate with basic adding functions to fill in the rest, we also have a sheet named master sheet shown below which currently we have been manually filling out with our order amounts and the weekly store takings. I managed to edit it to look at each week/sheet/tab (not sure what they are called) in a specific cell and pull the data across (very easy to do im sure but i found it very difficuly lol)

What im wanting to try to work out, is there a way to have the formula in say store takings B2, then drag it across using the bottom right corner and it auto copies the same cell it needs to look at B2 but auto changes the sheet its looking to to the next weeks date, as currently it keeps the same date for every cell but will just change to C2 then D2 in the formula etc. I've manually changed the date in every cell currently and it works find, i just want to see if theres an easier way than typing out 10 shops new sheets 52 times each. Im so lost right now so apologies if this all sounds confusing :-/

 

[AlanY]

You can use the indirect() function to do it.
Note that the sheet names needed to be the same as on the master sheet.

You can read more in

Point To Another Worksheet With Indirect

You need to grab cell B4 from one of many worksheets. You have to determine which worksheet, based on a cell label or a calculation, and you’re wondering if INDIRECT can point to another worksheet.

www.mrexcel.com

Book1
	A	B	C	D	E
1	25_12_2023	18_12_2023	26_12_2023	19_12_2023	27_12_2023
2	100	200	300	400	500
3	150	250	350	450	550
Master

Cell Formulas
Range	Formula
A2:E2	A2	=INDIRECT("'"&A$1&"'!"&"B2")
A3:E3	A3	=INDIRECT("'"&A$1&"'!"&"B21")

Book1
	A	B
1
2		100
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21		150
25_12_2023

Book1
	A	B
1
2		200
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21		250
22
18_12_2023

Book1
	A	B
1
2		300
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21		350
22
26_12_2023