Forum Discussion
Create Dynamic range from another dynamic range
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?
=INDEX($A:$A,COUNTA($A:$A))
