How use a dynamic range in a formula

[SimonWraight]

Hi all,

I have an Excel calendar for several teams. The columns are the dates. I want to, from a separate sheet, be able to enter a date range and count the days each person has annual leave for that range, so that I can calculate capacity.
I have dynamically created a range based on date range and team member, but putting that range into a formula is not working.
In H2 I have the value Sheet1!$I$4:Sheet1!$JB$4
This works:  =SUM(Sheet1!$I$4:Sheet1!$JB$4)
But this does not work =SUM(INDIRECT(H2)) 

This also does not work =SUM(INDIRECT("H2"))

Please could someone advise what I need to do. 

Thanks

Simon

[#2 · Accepted solution]

@SimonWraight 

Enter Sheet1!$I$4:$JB$4 in H2 and it shall work.

[#3]

Thanks Riny, works like a dream