How to use a SUM formula using an input from another sheet with an address function?-CodePudding

I am trying to SUM every number in a range on a different sheet. This range is dictated by an employee number, as well as the year; it changes for each employee and it also changes every year.

So I need the sum function to work like this:

=sum(first cell:second cell)

With the cells being variable with each employee.

I had used the ADDRESS function to obtain the cell values, but it put the sheet name between apostrophes, e.g. 'sheetx!'!A1 , which I thought caused the sum function to return an error.

To test this, I made a test SUM function using input from the CELL function, which returns the sheet value without apostrophes (e.g. sheetx!A1). I still got an error.

What am I doing wrong?

Dummy sheet available here (

also, in your question, you mention: 'sheetx!'!A1 which is also wrong due to two exclamation marks ! correct being: 'sheetx'!A1 unless your sheet is called sheetx!