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]

 

[twilley259]

Well that's helpful.


Might do, depending on what type of data will be in the blank area, how it's laid out, and so on.
Even if not, it will help you get a better answer if you can be super clear about your requirements, and not leave it to other people to guess what your data will look like in the future.


Fair enough, but it needn't take hours to populate 11 cells with typical examples of what the data might look like.


OK, so what do you want this to look like exactly, because I'm not clear ?
Give us an example please, one which has multiple rows with X, and show us what the results should be.

In the photo I posted is an example. Column P is populated with the X in multiple rows. My 2 photo's are clear examples. Sheet 1 Waste Treatment is selected. That is the title of column P on Sheet "WI" So sheet 1 should have formulas that look at what is selected in that drop down. Find that exact title in "WI" Row 2. Then return A,B,C of any row with an X.

 

[twilley259]

I don't know how to explain what I am trying to do much better than this:

 

[Gerald Higgins]

OK, so in the example in post #12 , you'd be returning about 25 rows of data to sheet 1, is that right ?

If yes, then there are probably various ways of doing it.
I would suggest using some VBA code to
1) clear out the target area in sheet 1, just in case there is any data left in there from last time you did this
2) apply a filter to your data
3) copy the filtered data from your report to sheet 1
4) remove the filter

Is this likely to be an acceptable solution for you ? I.e. using VBA ?

 

[twilley259]

OK, so in the example in post #12 , you'd be returning about 25 rows of data to sheet 1, is that right ?

If yes, then there are probably various ways of doing it.
I would suggest using some VBA code to
1) clear out the target area in sheet 1, just in case there is any data left in there from last time you did this
2) apply a filter to your data
3) copy the filtered data from your report to sheet 1
4) remove the filter

Is this likely to be an acceptable solution for you ? I.e. using VBA ?

I'm open to anything. This is for a manufacturing facility so I need it to be simple for the user. That's why I wanted them to make 1 selection from the drop down and the spreadsheet do the rest. Will the VBA be able to change on the fly? My goal is to have this spreadsheet open on the shop floor and anyone can come up and quickly make a selection and get the data they need without any other input. We have employees in the age bracket that computer usage is a struggle.

 

[oldbrewer]

how many items in the dropdown please eg 5, 15, 50

 

[twilley259]

how many items in the dropdown please eg 5, 15, 50

40

 

[oldbrewer]

job1

job2

job3

select a job from dropdown

job3

clean

x

x

tasks required > > >

1

clean

degrease

x

x

2

degrease

sandblast

x

x

3

drill

x

x

4

grind

x

5

thread

x

6

thread

clean obtained by

=IF(OFFSET($A$1,O2,MATCH($K$1,$B$1:$D$1,0))<>"",A2,"")

<colgroup><col span="4"><col><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[oldbrewer]

above post is a generic approach to save time - easy to expand to 40 jobs

 

[twilley259]

job1

job2

job3

select a job from dropdown

job3

clean

x

x

tasks required > > >

1

clean

degrease

x

x

2

degrease

sandblast

x

x

3

drill

x

x

4

grind

x

5

thread

x

6

thread

clean obtained by

=IF(OFFSET($A$1,O2,MATCH($K$1,$B$1:$D$1,0))<>"",A2,"")

<tbody>
</tbody>

I'll try to play with this. I'm not exactly sure how to make it work in my scenario but it's a start.

 

[oldbrewer]

you could make the output area a print area and use a macro to print it - attach it to a big red balloon with these words on it "click here to print task list