INDIRECT with condition or FILTER formula

[Dani_LobP]

Hi all,

I'm struggling to make some formula work, unsure if i am not applying the formula correctly.

for context:
I have a table that goes from C1 to GX. (x is dynamic since rows will be added over time)
First row is the headers of the table, so data goes from C2 until GX.
Then i have a column H, where will be Yes/No.

I want to be able to extract filtered rows into another tab from that table, ONLY when there is a Yes in the column H. But without adding column H if that was not clear.

When i do

=INDIRECT("Sheet1!C2:G"&COUNTA(Sheet1!C:C))

I am able to obtain the table as expected.
*I added COUNTA so the GX will be basically taking each new row and avoid having to enter a number each time...

The thing now is when i try to modify the above formula, with the filter condition.

If i do just:

=FILTER(Sheet1!C7:G10,H7:H10="Yes","")

I would be getting empty results. Unsure why.

And the idea that I want to try, is combine INDIRECT and FILTER, but when I do, I get VALUE error.

Not sure if i should FILTER(INDIRECT ... or INDIRECT(FILTER ... but get VALUE error both ways.

=FILTER(INDIRECT("Sheet1!C2:G"&COUNTA(Sheet1!C:C)),Sheet1!H:H="Yes")

=INDIRECT(FILTER("Sheet1!C2:G"&COUNTA(Sheet1!C:C),Sheet1!H:H="Yes"))

Hope it makes sense and someone can help me understand the formula better.

Thanks in advance!

 

[Fluff]

This formula

=FILTER(Sheet1!C7:G10,H7:H10="Yes","")

Will only return empty cells if you have "Yes" in col H on blank rows.
Can you post some sample data that shows the problem.

 

[Georgiboy]

Also when you mention 'Table', do you have a table object on the sheet or just some data in cells?

 

[Dani_LobP]

This formula

=FILTER(Sheet1!C7:G10,H7:H10="Yes","")

Will only return empty cells if you have "Yes" in col H on blank rows.
Can you post some sample data that shows the problem.

Thanks for the prompt reply Fluff!
I'm not able to upload excel file with the real data. But I can replicate with some mock up data if that helps.

C	D	E	F	G	H	I
Name	Country	Date	Team	Link	Confirmed?	Date2
Example1	C1	22/08/23	Team1	URL	Yes
Example2	C1	18/07/23	Team2	URL2	No
Example3	C2	15/08/23	Team2	URL3	Yes

So technically, I should be getting a new table in different sheet, where i would have as per below:

C	D	E	F	G
Name	Country	Date	Team	Link
Example1	C1	22/08/23	Team1	URL
Example3	C2	15/08/23	Team2	URL3

If I use IDIRECT formula, it works fine, as i would be getting it all, but, that's when i want to include the FILTER, to just get the rows with a Yes. Not sure if FILTER alone do the same job as INDIRECT thou, that's why I'm not sure if combining them is making some error of understanding.

Eventually the formula to filter data from first table, that is in another sheet, would bring just the columns from C to G, and would be just bringing those with H having a Yes. (actually will end up being those with a Yes in column H and empty cell in column I, but i thought once I would understand how to add condition i would be able to replicate to add more columns if required).

Hope this makes more sense now.

Thanks!

 

[Dani_LobP]

Also when you mention 'Table', do you have a table object on the sheet or just some data in cells?

Actually is a table as an object, but for some reason i was getting also error if using the table link instead of the column ... not sure why

 

[Fluff]

Just noticed the problem, you have not included the sheet name for col H, it Should be

=FILTER(Sheet1!C7:G10,Sheet1!H7:H10="Yes","")

 

[Georgiboy]

If working with tables then two options below:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Country	Date	Team	Link	Confirmed?		Name	Country	Date	Team	Link
2	Example1	C1	22/08/2023	Team1	URL	Yes		Example1	C1	45160	Team1	URL
3	Example2	C1	18/07/2023	Team2	URL2	No		Example3	C2	45153	Team2	URL3
4	Example3	C2	15/08/2023	Team2	URL3	Yes
5
6
7	Name	Country	Date	Team	Link	Confirmed?		Name	Country	Date	Team	Link
8	Example1	C1	22/08/2023	Team1	URL	Yes		Example1	C1	45160	Team1	URL
9	Example2	C1	18/07/2023	Team2	URL2	No		Example3	C2	45153	Team2	URL3
10	Example3	C2	15/08/2023	Team2	URL3	Yes
Sheet1

Cell Formulas
Range	Formula
J2:N3	J2	=FILTER(Table1[[Name]:[Link]],Table1[Confirmed?]="Yes")
J8:N9	J8	=DROP(FILTER(Table2,Table2[Confirmed?]="Yes"),,-1)
Dynamic array formulas.

This will remove the need for COUNTA or knowing the size of the range as formula will adjust to the table size.

 

[Dani_LobP]

Just noticed the problem, you have not included the sheet name for col H, it Should be

=FILTER(Sheet1!C7:G10,Sheet1!H7:H10="Yes","")

oh. right. makes sense.
So i actually dont have to use INDIRECT since FILTER does the same thing actually. right?
if i have the Table1, i can actually select the header columns instead of the C:G with numbers, right?
And last question, if i want to add another condition, i can just add (condition1)*(condition2) , right?

Thanks again for your help!

 

[Dani_LobP]

If working with tables then two options below:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Country	Date	Team	Link	Confirmed?		Name	Country	Date	Team	Link
2	Example1	C1	22/08/2023	Team1	URL	Yes		Example1	C1	45160	Team1	URL
3	Example2	C1	18/07/2023	Team2	URL2	No		Example3	C2	45153	Team2	URL3
4	Example3	C2	15/08/2023	Team2	URL3	Yes
5
6
7	Name	Country	Date	Team	Link	Confirmed?		Name	Country	Date	Team	Link
8	Example1	C1	22/08/2023	Team1	URL	Yes		Example1	C1	45160	Team1	URL
9	Example2	C1	18/07/2023	Team2	URL2	No		Example3	C2	45153	Team2	URL3
10	Example3	C2	15/08/2023	Team2	URL3	Yes
Sheet1

Cell Formulas
Range	Formula
J2:N3	J2	=FILTER(Table1[[Name]:[Link]],Table1[Confirmed?]="Yes")
J8:N9	J8	=DROP(FILTER(Table2,Table2[Confirmed?]="Yes"),,-1)
Dynamic array formulas.

Thanks! this could help a lot too!

 

[Fluff]

So i actually dont have to use INDIRECT

Nope. INDIRECT is a function you should avoid at all costs.

if i want to add another condition, i can just add (condition1)*(condition2) , right?

That's right.