I have an excel inventory spreadsheet that is used heavily and I would like some advanced search functions. Ideally I would like a separate sheet or form that is very basic so the user doesnt get confused.
Here is an example of the spreadsheet layout:
Item#, Qty truck 1, Qty truck 2, Qty truck 3, cost, etc...
1234, 3, 2, 4, $5, etc...
The normal search function allows us to search by part number and see the entire row, of course. But the row has 50 columns and we dont need to see all that data. I would like to have a simple search form that allows us to put in the item number and perhaps a truck number. So then I am returned only the qty of that item in that location and if the item is not present in that location a message stating so.
For example, if I put in my criteria item 1234, truck 3, I want to see the item returned and that there is 4 available in that truck.
Maybe it would be easier to search by item # but then select the other columns that I want returned in the results. Maybe I want to see what trucks have it and the reorder points etc..
I hope im being clear in what im requesting. Let me know if you have questions. Thanks in advance for any help.