How use a dynamic range in a formula

New Contributor

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. 




2 Replies
best response confirmed by SimonWraight (New Contributor)


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

Thanks Riny, works like a dream