Create Dynamic range from another dynamic range

[Juli Reid]

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.

[#2]

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

[#3]

@Riny_van_Eekelen 

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

[#4]

@Juli Reid 

Do you mean

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

?

[#5]

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.