Formula to Lookup Based on Value and Return Data Based on Value

[twilley259]

Hello,
I am needing help with a complex lookup formula. I have a table of data with information in it shown below.

[/URL][/IMG]

So on sheet 1. I have a drop down with all the work cells listed. Whenever I select the title from the drop down, I want a formula to find that title in Row 1 then look in that column for the X's I have placed. These X's show which work instructions pertain to that job. Any field with an X I want the formula to return the hyperlink in column A. I hope this makes sense. Basically, you make a selection, excel finds that title, then if there is an X in that column it returns the corresponding A value.

[/URL][/IMG]

 

[oldbrewer]

not that hard to print the tasks without the blank spaces

 

[twilley259]

not that hard to print the tasks without the blank spaces

I don't want printing to be an option at all honestly. That puts uncontrolled documents on the floor and creates issues from auditing.

 

[oldbrewer]

SO HOW DO THE WORKERS RECORD the steps - I was in QA and understand uncontrolled copies - we used to watermark them "only valid until 23:59 today's date

 

[twilley259]

SO HOW DO THE WORKERS RECORD the steps - I was in QA and understand uncontrolled copies - we used to watermark them "only valid until 23:59 today's date

There are no steps. What I am trying to accomplish is the removal of work instructions books on the shop floor that must be controlled and constantly updated. So the idea is sheet 1 has the dropdown containing every work cell in our facility. The WI tab contains the hyperlink to a PDF, the title, and the rev level along with all the approval data. So what I want to do is use that tab add a column for each work cell then use an X to mark if that WI is required in that cell. Then everything will be hidden, and operators on the floor only see sheet 1. They make a selection of what work cell and it returns a link to the WI PDF, Title, and Rev level. This gives our QC department 2 things to update (hyperlink to PDF and this sheet) instead of 40 books on the shop floor.

 

[oldbrewer]

ummmmm - so workers check if their WI's are up to date ? Not being awkward sir, but I think I am still missing something....

 

[twilley259]

ummmmm - so workers check if their WI's are up to date ? Not being awkward sir, but I think I am still missing something....

No workers need to have access to their WI at any given time in case of a new employee, they want to reference how something is supposed to be done, etc. Quality/Engineering ensures they are up to date. Currently we have hard paper copies on the floor that must be controlled and someone has to manual print and update each individual book when there is a change. If I can get this spreadsheet to work when a change is made our QC dept. only has to update the sheet that's live on the network and shop floor versus the 40 hard copy books.

 

[oldbrewer]

so re my post 17 what specifically do you want to do - tell a worker if a particular WI has been revised ?

 

[twilley259]

so re my post 17 what specifically do you want to do - tell a worker if a particular WI has been revised ?

No. I want my sheet to pull all the required WI for each work cell based on the work cell selected in sheet 1. Let me try to generate an example manually and upload a photo.

 

[twilley259]

Let's try this:




This is what Sheet 1 should look like after the formulas populate the data:





So in short, I want the formulas on Sheet 1, to reference the work cell the operator selects in the drop down. Then, locate that title in "WI" and return columns A,B,C in the corresponding rows with an X. What this will allow me to do is basically, generate my current WI book table of contents on the fly, but with a hyperlink to the PDF work instruction that can be viewed not edited. Does that make sense?