#CALC! error when using INDIRECT (the cause?)

[sokolum]

Formula work great, until I copying it another sheet.

The formula works great with FILTER, it produces a list of the values it finds matches with "x".
It tells which colors for each month where the "X" has been added.
=TEXTJOIN(CHAR(10),TRUE,FILTER(A:A,(INDIRECT((CHAR(64+MATCH(B10,A1:Y1,0)))&":"&(CHAR(64+MATCH(B10,A1:Y1,0)))))="x"))

When I copying the formula to a Newsheet, I get a #CALC! error, the modified formula. The Input H2 is done on the new Sheet. I disected the formula, cant find the issue.
=TEXTJOIN(CHAR(10),TRUE,FILTER(Color!B:B,(INDIRECT((CHAR(64+MATCH(Newsheet!H2,Color!A1:Y1,0)))&":"&(CHAR(64+MATCH(Newsheet!H2,Color!A1:Y1,0)))))="x"))

Sheet: Color

	A	B	C	D	E	F
1		Januari	Februari	March	April	May
2	red			x	x
3	orange	x
4	yellow		x		x
5	green		x		x
6	blue	x		x
7	indigo		x		x	x
8	violet			x	x	x
9
10	Input:	Januari

 

[Fluff]

Why not use index rather than indirect

=TEXTJOIN(CHAR(10),TRUE,FILTER(Color!A2:A100,INDEX(Color!A2:Y100,,MATCH(H2,Color!A1:Y1,0))="x"))

 

[sokolum]

Filter is very easy to use and requires less input, it finds and it is capable of showing all the values it has found:
=FILTER(A1:A9,B1:B9="x")

Issue still remains.

 

[Fluff]

Are you saying the formula I posted does not work?

 

[sokolum]

This formula is not working, to me it seems to stop at INDIRECT.
This example I made a typo by writing B:B, should have been in this example A:A.

FILTER is working when adding manual the cell locations
MATCH is working too.
CHAR is working too.
But the issue start when using INDIRECT, as i want to capture have the whole COLUMN, e.g.: B:B

=TEXTJOIN(CHAR(10),TRUE,FILTER(Color!A:A,(INDIRECT((CHAR(64+MATCH(Newsheet!H2,Color!A1:Y1,0)))&":"&(CHAR(64+MATCH(Newsheet!H2,Color!A1:Y1,0)))))="x"))

What this formula will do:

- Look at your manual input: H2
January

- It find a MATCH (January) by searching at the ROW A1:Y1 (actual list is much bigger)
- It then count the COLUMN and using CHAR to get the COLUMN letter
- with INDIRECT it takes the 'text' input as a value to be used by FILTER to search for that "x"
- Where FILTER reads the "x", it reports back the value it reads in Column B:B

 

[Fluff]

Did you actually try the formula I suggested, rather than that monstrosity?

 

[sokolum]

Did you actually try the formula I suggested, rather than that monstrosity?

Let me give it a tray.. currently not able to take a look directly. Will report on this.

 

[Fluff]

Ok,

 

[sokolum]

Why not use index rather than indirect

=TEXTJOIN(CHAR(10),TRUE,FILTER(Color!A2:A100,INDEX(Color!A2:Y100,,MATCH(H2,Color!A1:Y1,0))="x"))

Works excellent.
I tried to get this done with INDEX, but was not able to get my head around when doing the ARRAY!

Note: Last time i did something like such in Excel was about 10 years ago...

Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.