Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

How use a dynamic range in a formula

Copper 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. 

Thanks

Simon

 

2 Replies
best response confirmed by SimonWraight (Copper Contributor)
Solution

@SimonWraight 

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

Thanks Riny, works like a dream :)
1 best response

Accepted Solutions
best response confirmed by SimonWraight (Copper Contributor)
Solution

@SimonWraight 

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

View solution in original post