I work in a machine shop. I have a file that could have text values of "Saw", "Lathe", etc. in one of 15 columns. I want to be able to search for the text value I choose and use an OFFSET function to reference the cell directly to the right (setup and run times). This will allow me to make a chart of all machining centers and SUM the number of hours required for each in a week (creating a production and capacity chart). I know that I can do this with a series of IF,INDEX,MATCH formulas that checks each column for the name of the machine as text to give me the information...
(i.e. =IF(INDEX('Quote Book'!B:B,MATCH('Production Schedule'!B2,'Quote Book'!A:A,0))="SAW",INDEX('Quote Book'!C:C,MATCH('Production Schedule'!B:B,'Quote Book'!A:A,0)),IF(INDEX('Quote Book'!E:E,MATCH('Production Schedule'!B2,'Quote Book'!A:A,0))="SAW",INDEX('Quote Book'!F:F,MATCH('Production Schedule'!B2,'Quote Book'!A:A,0)),"No saw so far..."))
However, the real chart has up to 15 operations and we have more than 10 machines, so you can imagine that this formula would be straining for my processor as we have 300 parts running through the shop at any given time... I've attached a simple representation of the issue and the "Saw" columns have an example of the solution I already know is possible. If you can help me with a different proof of concept I should be able to take it from there.
Bookmarks