Help!need formula to get multiple row headers

[mrettler]

I have an inventory sheet that we put together. It lists the equipment down the left(multiple equipment each with a unique name ID)column A. The parts(all with unique ID's) is listed across the top in row 1. If the part belongs on the equipment we marked the intersecting cell with a 1. There can be multiple parts per piece of equipment. I am trying to do a vlookup for the equipment and give a list of the parts available. The lookup list is in a cell with a dropdown box containing all the equipment.

What is the easiest way to go about this. This is what I have done so far. I made a cell that got the MAX from the row.(which of course is 1) then I did a COUNTIF it was = to 1 to get the number of parts avialable. Then I made this to solve and get the parts in that row =IF(ROWS(N$8:N8)>$M$8,"",INDEX($B$1:$K$1,SMALL(IF( $B$3:$K$3=$L$8,COLUMN($B$3:$K$3)-COLUMN($B$3)+1,ROWS(N$8:N8))))

This formula works for one row but I can't figure out how to get the loolup equipment and tie that row in.

I did figure out how to use the lookup and tie the MAX and COUNTIF equations. It is
=MAX(INDEX(table2[[Part 1]:[Part 10]],MATCH($N$4,$A$2:$A$11,0),0))

I haven't worked much in excel but have been taking a self educating crash course for the last two weeks. I believe I have all the parts just not putting it in the right order or missing a minor part to get the last part to work.



Thanks,Matt

[Pecoflyer]

Hello
the formula you are using is an array formula.
Have you committed it with Ctrl+Shift+Enter (adding {} manually will not work)

[mrettler]

Yes I can get this array to work. The problem is that it only works for the single row that it is set up for. It is in a small table that shows available parts for a specific piece of equipment. I would like to add the lookup (or match) to find the row the equpment is in, then tell me the parts that are available in that row. Right now I don't have the row tied to the piece of eqipment

I will try to post what I have. We are not set in stone on this and if there is an easier way to set up the initial table I am all ears. We are just starting to track our inventory and I thought this would help the guys.

Matt

[mrettler]

The cell N4 is the dropdown box that has a list of equipment. Cell N8 works but only looks at the row that I set it up for. I would like to be able to reference the row by your pick of the piece of equipment from cell N4 and then populate the list with the available parts.

Is this possible?!?!

I hope this works, Matt

Inventory test sheet.xlsx

[xor]

What should be returned for Tractor 8. Part 6 and Part 8?

If not, then what?

[mrettler]

Yes each tractor should return the parts that are marked with a 1


Matt

[xor]

Take a look at the attached.

[mrettler]

Originally Posted by xor

Take a look at the attached.

I am not near my computer until Sunday afternoon. I will check it out then. From the little I do see on my phone it looks as if you had a helper cell. I can't see the formulas but the real sheet will be much larger and the parts associated with each tractor could end up anywhere in the sheet. I am sure your formulas account for that but just an FYI. If I can grab any piece of equipment from a drop down list and it will show all parts available, that would be awesome!!


Thanks, Matt

[xor]

I upload a new version because I had forgotten that (in the previous file) I had converted your table to a range without setting it back again.

In the present file I have also put a few comments which might be of some help.

Please disregard my first uploaded file.

[mrettler]

xor, this is exactly what I needed. Still not sure I understand everything you did there but I should be able to figure it out with a little more looking. Like I said, I have really only been working in excel for about 2 weeks now and it is all self taught.

I will put this to work on a larger sheet that involves our real application. One question I have is can I use these forumulas on a separate sheet within the same workbook? My goal is that the employees can have a sheet that looks up parts(and gives location and quantity), or they can look up the piece of equipment and find the parts we have on hand for it.

Thanks again, Matt

[xor]

Not quite sure what you mean here:

.... One question I have is can I use these forumulas on a separate sheet within the same workbook? ......

Isn't that exactly what the example does?
Do you mean if the formulas can be used on more sheets within the same workbook?

One more question by the way - will your table always contain only "1" or blank?

[mrettler]

Yes the table would always include either "1" to identify the intersecting row of the tractor and the parts available or it would be left blank.

The other question was in reference to the formula's working on one sheet but the actual tractors and parts on a different sheet or tab. I would like there to be two and maybe three tabs (or worksheets) in the file. The shop mechanics would use sheet 1(possibly labeled parts lookup, and would only see the popup box and the parts available. Sheet two would be used by the shop foreman and would be the actual table with all the tractors and parts. This table will end up very large as we have close to 100 pieces of equipment and hundreds of parts.

Matt

[xor]

Could you possibly upload a copy of the real application (with an indication where you want the result to appear)?

[mrettler]

Here is basically what I am looking for.

Matt

[xor]

And here is my proposal.