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

# How use a dynamic range in a formula

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"))

Thanks

Simon

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

# Re: How use a dynamic range in a formula

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

# Re: How use a dynamic range in a formula

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

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

# Re: How use a dynamic range in a formula

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