Document Matrix

[spr1nger]

I have a list of documents in an Excel Spreadsheet (a matrix per se), listing all documents with their #'s, document titles, what departments each belong to, & revision date. On a 2nd Tab, I'd like to return (link if anything changes on main tab) all documents that belong to a specific department. The way I set up the spreadsheet is with column headings for doc #, title, rev date, then the subsequent columns are each department. The rows are the various documents & I placed an "X" in the column of the department it pertains to. What formula can I use on the 2nd tab to return the list of a specific department?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=FILTER(Sheet1!B2:B100,INDEX(Sheet1!E2:I100,,MATCH(A2,Sheet1!E1:I1,0))="X")

Where A2 has the name of the department to want to look at.

 

[spr1nger]

=FILTER(Sheet1!B2:B100,INDEX(Sheet1!E2:I100,,MATCH(A2,Sheet1!E1:I1,0))="X")

I copied & pasted your formula & replace A2 after the MATCH, with a department name, but that didn't work. i'm doing something wrong obviously. I will try again tomorrow as I have to leave work now for an appointment. will touch base with you then. thank you

 

[Fluff]

In what way didn't it work?

 

[spr1nger]

Hi & welcome to MrExcel.
How about

=FILTER(Sheet1!B2:B100,INDEX(Sheet1!E2:I100,,MATCH(A2,Sheet1!E1:I1,0))="X")

Where A2 has the name of the department to want to look at.

Ok, here is a snapshot of the error I'm getting. My 'sheet1' is named Doc Matrix so tried changing the formula to reference that. what am i doing wrong?

 

[Fluff]

You need to put Purchasing in quotes, so it should be "Purchasing"

 

[spr1nger]

You need to put Purchasing in quotes, so it should be "Purchasing"

ok 1 sec

 

[spr1nger]

You need to put Purchasing in quotes, so it should be "Purchasing"

now it is a value not available error

 

[Fluff]

Purchasing is in col M so the formula cannot find it in the range E1:I1

 

[spr1nger]

? i can't figure it out.