Create Dynamic range from another dynamic range

Brass Contributor

I have a schedule availability calendar where the top row is the times and the first column is the name of the members in the group.  This calendar has a sheet for each day of the week and a sheet where the admin can manage the member names.


MemberCount is a single column dynamic range that contains the member names and will be managed by the group admin.


I want to define a single column dynamic range for each of the daily schedule calendars using the MemberCount range to define the number of rows and populate the values.   I tried a couple different ways to do it but was unsuccessful.




4 Replies

@Juli Reid Not entirely sure I understand but when you say that the MemberCount is a dynamic range, is it that it contains a formula in the first cell and results are spilled down the column? And you see a thin blue line around the whole area?


If so, let's say the first cell in MemberCount is on Sheet1!A1, you can then refer to that cell and add a # sign at the end to return the entire list on one go. Thus, =Sheet1!A1#


Or perhaps you have a structured Excel table called Table1 with a column called "MemberCount". Then you could refer to that column by its name as. Something like =Table1[MemberCount]


Finally, you could set-up a (dynamic) named range for MemberCount and refer to it as =MemberCount on your other sheets. Though, you can not use either of these methods inside a structured Excel table as all will spill their results to multiple cells.


Plenty of options. Up to you to clarify what you are dealing with.



MemberCount is a dynamic named range. 


I didn't explain what I am trying to do very well, so to clarify.   I want to create a new Dynamic named range in a specific column and have it automatically populated with the data in MemberCount.


I thought it could possibly be done in the formula that defines the name.   If so, how would I change the formula below to reference MemberCount and automatically pull in the data?









@Juli Reid 

Do you mean



It's the same thing. You don't have to use the Sheetx! reference if the range is on the same sheet as the formulas you will be using it in. I will be using the sheet reference as the MemberCount range will be on sheet 8.