Forum Discussion
Creating a workorder tracking sheet linked to a work order
CPryde if you only want to print the work order details from the info entered into the tracking sheet, you can create the layout for the printed paper on a new Excel sheet. In one cell let the user select or enter a work order number from the tracking sheet. Then use lookup formulas to fill in the information on the work order sheet, then print that sheet.
For example, if the tracking sheet is called Work Order List, create a new sheet called Print Work Order. In cell B1 enter a valid work order.
In cell B3, pull the work order description with a Vlookup formula.
Find a sample file attached.
IngeborgHawighorstThank you for your response. Could I ask for more assistance. I have attached the work book I have been working in.
The work order tracking form tab is where we want the original information to be entered. (say line #4) Once it is entered it needs to flow to the template tab. From there this would be printed. (please disregard the other tabs in this workbook)
Then my thoughts were to enter information in the work order tracking form on to line 5 and again this information would then flow into the template and again printed. So the template tab would be reused every time something is entered on the work order tracking tab.
Can this be done? Or do you have other ideas. If you want to change up my workbook you are more than welcome.
CPryde ,
please find a suggestion attached.
I have inserted a new column B and a new column F. In this column you enter the number for the column in the tracking sheet that has the data you want.
In cell C8 I have used the Vlookup formula
=VLOOKUP($G$1,'Work Order Tracking Form (2 (3)'!$B:$N,'Workorder Template'!B8,0)
Since the Vlookup uses column B as the start, B =1, C = 2, etc. The job site is in column G, which is column 6, so cell B8 has the value 6. You can hide the columns B and F after you have entered the numbers. The same formula has been copied to the cells for tenant, suite, tenant hours, brief description and tech.
Now all you need to do is enter the Work Order number into the template sheet cell G1.
If you want the latest work order number to appear in cell G1 automatically, you can use a formula like this
=INDEX('Work Order Tracking Form (2 (3)'!B:B,MATCH(99^99,'Work Order Tracking Form (2 (3)'!B:B,1))
This will find the last row with a work order number in column B. Sometimes you may want to print a particular WO, but you don't want to destroy the formula. You could enter the override work order number into cell H1 and only if H1 does not have a number, use the lookup.
=IF(ISNUMBER(H1),H1,INDEX('Work Order Tracking Form (2 (3)'!B:B,MATCH(99^99,'Work Order Tracking Form (2 (3)'!B:B,1)))
Note that any other data that you enter into the template will remain when a new Work order is entered. You would have to copy and paste the template to a new sheet to retain the data.
Hope that makes sense. Attached is the file with the helper columns B and F hidden.