Changing Cell References based on value in drop down box.

[Heinrad]

Good Afternoon,

I have come unstuck with an Excel sheet we have after trying to automate part of the document.

This spreadsheet tracks costs over a year based on what week it is. Each member of staff has a spreadsheet seperate to this one where they fill in data, and that data is referenced in this other workbook and populates accordingly.

For the overall year, it works fine, it looks at the individuals spreadsheet and adds together all 52 weeks then produces the total.

My boss asked me to produce numbers for the past 4 weeks. To quickly get it done, I simply changed the cell references to the current week and the entry from 4 weeks ago and summed them together. The formula looks something along the lines of =SUM('filelocation\[DOCUMENT.XLS]Sheet1'!C5:C56)

I was wondering if there was a way where, if I set the week number to '10', either by text entry or drop down menu, it could change the cell references to match without me having to edit each individual user. In this case, if week 10 was selected, the formula would change to the sum of C11:C14.

Thank you for your help.

 

[AlanY]

have a look of the indirect() function,

e.g =sum(A4:A7) can be rewritten as

=SUM(INDIRECT("A"&A1+1&":A"&A1+4))

with 3 as variable in Cell A1

 

[Heinrad]

Thanks for the response, that does look like what I am looking for. Does this function work when referencing a different workbook? If so, how does that look?

I'm not sure how to build the function when it uses ('filelocation\[DOCUMENT.XLS]Sheet1'!C5:C56) to look at a seperate file.

 

[AlanY]

for example you have data in book1 Sheet1 like this

Excel 2013/2016
	A
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
Sheet1

and you wanted to sum up 4 weeks after week 10 in book 2

Excel 2013/2016
	A
1	10
2
3
4	50
Sheet1

Cell Formulas
Range	Formula
A4	=SUM([Book1.xlsx]Sheet1!$A$11:$A$14)

to have it dynamically, e.g. week 10 in A1, with indirect()

Excel 2013/2016
	A
1	10
2
3
4	50
5
6	50
Sheet1

Cell Formulas
Range	Formula
A6	=SUM(INDIRECT("'[Book1.xlsx]Sheet1'!A"&A1+1&":A"&A1+4))