• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter Function

Status
Not open for further replies.
T

TASTEIN00

New Member
I have a table that has 30+ columns and I'm using the filter function, however; I want to return non adjacent columns on different workbooks with two consistent columns on every sheet and the third would vary. I know about using the {} to return specific columns, but is it possible to use either match or xmatch to pick up the third column versus hardcoding the column numbers in.

For example...this works...=FILTER(INDEX(Transactions, SEQUENCE(ROWS(Transactions)), {1,2,15}),((ISNUMBER(SEARCH(" Ending Balance",Transactions[Transaction]))))*(Transactions[Carter Scholarship - Sloan]<>0))

But instead of hardcoding that third column for each different of the 30+ worksheets, is there a way to make that dynamic? In this example, column 15 = "Carter Scholarship - Sloan".

And on each worksheet, I have the table header in cell A1 to identify the worksheet. In this case "Carter Scholarship - Sloan" would be in A1, that I could use as the lookup value, with the lookup array being Transactions[#Headers].

Thank you for any help in advance!

Ted Stein
 
You could try
=FILTER(CHOOSECOLS(Transactions,1,2,match(a1,Transactions[#Headers],0)),((ISNUMBER(SEARCH(" Ending Balance",Transactions[Transaction]))))*(Transactions[Carter Scholarship - Sloan]<>0))
 
Fluff13 said:
You could try
=FILTER(CHOOSECOLS(Transactions,1,2,match(a1,Transactions[#Headers],0)),((ISNUMBER(SEARCH(" Ending Balance",Transactions[Transaction]))))*(Transactions[Carter Scholarship - Sloan]<>0))
Click to expand...

Thank you so much, that worked. I was trying to figure out how to use the CHOOSECOLS function, but couldn't get it right.
 
Fluff13 said:
Glad to help & thanks for the feedback
Click to expand...

I have one more question along the same lines...

So this is my current series formula: =SERIES(,'Ag Program Scholarship'!$B$4:$B$21,'Ag Program Scholarship'!$C$4:$C$21,1). Column B is my labels and Column C is my values.

So my spilled data that is returning, is it possible to use that in a formula as data labels/values instead of hardcoding the ranges in. Everything that I'm seeing is to use named ranges, but I don't really want to have to create the same named range for 30+ sheets. Especially if new scholarship funds were added, and a new worksheet had to be added, we would have to create a named range for the new sheet.

I've tried using the hash, but it didn't like that either. It would be nice so it would automatically change each month when new data was added. And you can't return a spilled range into a table, so that eliminates the idea of creating a table.

Hopefully this makes sense to you.
 
Please be noted of the forum rule that "One post One question".

You are requested to open a new thread for your new question.

This thread is closed.

Regards
 
Status
Not open for further replies.
Back
Top