VLOOKUP only pulling data from a specific work center

[Hannah_Pham93]

Hi all, I'm trying to create a spreadsheet that allows me to dump data from a report into the first tab and then using vlookups (I assume that's the best way to do it, but maybe it's not) to pull data to different tabs. Each tab represents a different machine and the data being pulled from the first tab will be open manufacturing orders, priority, qty, orders, etc. So for example, the first tab is labelled 5526 (the work center code) -- I want to make a formula to only pull data from the master tab for WC 5526. How can I do this?

A note, I am currently relying on sorting the table per WC, but every time I dump in new data I have to re-filter each tab, so I'm trying to prevent that manual work.

Tab with all the data:

Work Center (WC) tab where I need the formula:

 

[knoharra]

Try making your Dispatch List Master spreadsheet into a table so that your Master file is dynamic and you don't need to change the range with new additions, I named mine Master. Then make sure each WC tab has headers that match the Master sheet. Then, in 5151 tab for example, use this in your upper-left-most non-header cell (A5 as above):

=SORT(FILTER(Master,Master[WC]=5151),6)

This sorts by the 6th column of the Master Tab (I used due date, I'm not sure what you wanted to sort by). I didn't account for the WO# that doesn't appear in your visible Master Sheet. See my example in screenshots following.

Master Table

WC Range

 

[Hannah_Pham93]

Try making your Dispatch List Master spreadsheet into a table so that your Master file is dynamic and you don't need to change the range with new additions, I named mine Master. Then make sure each WC tab has headers that match the Master sheet. Then, in 5151 tab for example, use this in your upper-left-most non-header cell (A5 as above):

=SORT(FILTER(Master,Master[WC]=5151),6)

This sorts by the 6th column of the Master Tab (I used due date, I'm not sure what you wanted to sort by). I didn't account for the WO# that doesn't appear in your visible Master Sheet. See my example in screenshots following.

Master Table
View attachment 120596

WC Range
View attachment 120597

Thanks for your input, I really appreciate it! For some reason, I can't seem to get this to work though I put my data on a table, like you recommended, see below. But I keep getting errors when I try your formula. When I type out the formula, am I literally typing out "Master" or do I need to click on the tab and highlight all of the data? Sorry, I'm really not great with Excel (clearly ). Thank you again!!!

Also, Job and WO# is the same thing, I just changed the verbiage on each tab. Maybe that's part of the issue?

 

[Hannah_Pham93]

Try making your Dispatch List Master spreadsheet into a table so that your Master file is dynamic and you don't need to change the range with new additions, I named mine Master. Then make sure each WC tab has headers that match the Master sheet. Then, in 5151 tab for example, use this in your upper-left-most non-header cell (A5 as above):

=SORT(FILTER(Master,Master[WC]=5151),6)

This sorts by the 6th column of the Master Tab (I used due date, I'm not sure what you wanted to sort by). I didn't account for the WO# that doesn't appear in your visible Master Sheet. See my example in screenshots following.

Master Table
View attachment 120596

WC Range
View attachment 120597

I'm sorry, I re-read your initial response and saw that you said to make sure each tab has all of the same headers. I went ahead and did that and entered the formula you gave me but still got this error message. Any advice? Thank you again for your help!

 

[knoharra]

I'm sorry, I re-read your initial response and saw that you said to make sure each tab has all of the same headers. I went ahead and did that and entered the formula you gave me but still got this error message. Any advice? Thank you again for your help!

View attachment 120652

I can't see your table name in the screenshot. It appears you renamed the sheet (tab) but possibly not the table itself? Try selecting the table data using the entire table data for 1st filter input and the WC column for the 2nd.

 

[Hannah_Pham93]

I can't see your table name in the screenshot. It appears you renamed the sheet (tab) but possibly not the table itself? Try selecting the table data using the entire table data for 1st filter input and the WC column for the 2nd.

Ahh, yes, this worked!! You are the best, thank you so much

 

[knoharra]

Ahh, yes, this worked!! You are the best, thank you so much

Happy to help, glad you got it to work. I'm pretty new to less basic functions as well.

 

[Fluff]

What version of Excel are you using, as 2016 doesn't have the Filter function.

 

[knoharra]

What version of Excel are you using, as 2016 doesn't have the Filter function.

I'm using 365

 

[Fluff]

I was referring to the OP, who's profile shows 2016 which does not have the Filter function.