How to use formula in cell with formula

[claven123]

I want to list the names of members present. I have rows (2-43) with names and columns based on dates and if they are present I put a X in that cell in the column for the date. I want to list only those members with X's in a list.
I was able to create a list in column A with this formula =IF(B2 = "X", A2, ""). This listed the names from A2 down to A43. With blanks for those not present.

Now in cell D2, I have placed this formula. Which lists the names in the D2 going down. So, if 6 people were present, I get a list of 6 names in cells D2-D7. My issue is that the next cell without a corresponding name (D8) will give me a #NUM! error. If I remove the formula in column A the error resolves.

=IF(ROWS(D$2:D2)>COUNTA($A$2:$A$25),"",INDEX($A$2:$A$25,AGGREGATE(15,6,(ROW($A$2:$A$25)-ROW($A$2)+1)/($A$2:$A$25<>""),ROWS(D$2:D2))))

This formula is a bit too complex for me to be able to fix it, any suggestions.

D

 

[AhoyNC]

Try:
Also, shouldn't your range be A2:A43?
What version of Excel are you using?

=IFERROR(INDEX($A$2:$A$25,AGGREGATE(15,6,(ROW($A$2:$A$25)-ROW($A$2)+1)/($A$2:$A$25<>""),ROWS(D$2:D2))),"")

 

[claven123]

Try:
Also, shouldn't your range be A2:A43?
What version of Excel are you using?

=IFERROR(INDEX($A$2:$A$25,AGGREGATE(15,6,(ROW($A$2:$A$25)-ROW($A$2)+1)/($A$2:$A$25<>""),ROWS(D$2:D2))),"")

Oops I copied the wrong cell, yes, it is A2:A43
Office 365

This is what I have.

=IF(ROWS(D$2:D2)>COUNTA($A$2:$A$43),"",INDEX($A$2:$A$43,AGGREGATE(15,6,(ROW($A$2:$A$43)-ROW($A$2)+1)/($A$2:$A$43<>""),ROWS(D$2:D2))))

D

 

[claven123]

With your formula, I get 0 where there is spaces if I have a formula in column A, without formula I get the correct output.

D

 

[AhoyNC]

How about posting a small sample of your data,
What do you have in each column?
What column has your formula IF(B2 = "X", A2, "")?
What version of Excel do you have (if Excel 365 or 2021 there may be a better formula)?

 

[AhoyNC]

If you have names in one column and an x in the date column then why not use something like the example below.

Book2
	A	B	C
1	Names	Date
2	Name1	x	Name1
3	Name2		Name4
4	Name3		Name7
5	Name4	x	Name8
6	Name5		Name10
7	Name6
8	Name7	x
9	Name8	x
10	Name9
11	Name10	x
12	Name11
13	Name12
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=IFERROR(INDEX($A$2:$A$43,AGGREGATE(15,6,(ROW($A$2:$A$43)-ROW($A$2)+1)/($B$2:$B$43="x"),ROWS($C$2:C2))),"")

 

[Fluff]

Office 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[claven123]

I updated my account details, sorry about that.

AhoyNC's solution worked. I was trying to combine the two and that accomplished it perfectly. Much better solution than what I was working on.

Thanks for the help, very appreciated.

D

 

[Fluff]

As you have 365 you could also use

Fluff.xlsm
	A	B	C
1	Names	Date
2	Name1	x	Name1
3	Name2		Name4
4	Name3		Name7
5	Name4	x	Name8
6	Name5		Name10
7	Name6
8	Name7	x
9	Name8	x
10	Name9
11	Name10	x
12	Name11
13	Name12
Master

Cell Formulas
Range	Formula
C2:C6	C2	=FILTER(A2:A100,B2:B100="x")
Dynamic array formulas.

 

[AhoyNC]

Glad we could help. The FILTER option that @Fluff posted would be the better way to go since you have Excel 365.
Thanks for the feedback.