Not sure what to call this... but i need it :)

[blueice2627]

Hi All,

I work in a hotel and I am trying to create a simple tracking worksheet that will keep track of work orders that we give to our engineering department to get things fixed in the hotel. I know that this would probably be easier to do in access, but given the staff that this will be going to to update it, i would prefer to keep it as simple looking as possible to not confuse people. Excel gives some people anxiety and if i threw some access at them, it would probably blow their mind...:wink:

Essentially what i have in the attached spreadsheet is:
Cover Sheet - A one page document that will be sent out showing tasks completed this day as well as current open tasks
Daily Log - staff will log the work orders from that day onto this sheet. If the work order is completed, when they mark "Yes", it will transfer the work order to the Completed Tab
Completed - A running list of all completed tasks since the beginning of the sheet (likewise if something is marked Yes on completed on this sheet, if it is changed to "no" it will move back to the Daily Log

My question that i am trying to solve is, i want the Cover Sheet to auto populate work orders that are completed on the selected day from the completed tab as well as show all the current on going work orders that have not been completed that will appear on the Daily Log tab. Normally i would just do some sort of vlookup function to pull the date, but since there will be multiple rows with the same date, and since there can be multiple times that can also be duplicates, i'm a little stumped on figuring out how to put a formula together for this. Also, i have a formula just to reference the ongoing work orders, but what i have found is, whenever an item gets marked as complete, it will give error messages on this side due to the deleting of the row when transferring that row to the completed tab.

I feel like i'm missing something very simple here, but for whatever reason i'm baffled at this point. Any help would be appreciated!

Thanks!

 

[dmt32]

Hi,
sounds like you are seeking a HelpDesk Issue tracking system. Such systems can be quite complex but always worth seeing what others have posted out there which you may be able to adapt for your particular need.

here is one site where can download the workbook:https://chandoo.org/wp/issue-trackers/

but with more searching, there probably are others.

Hope Helpful

Dave

 

[Zack Barresse]

Could you please be more specific about your data structure and how you want to fire this off?

 

[blueice2627]

Sorry, gone is the days where your work internet is faster than your home internet.... Been trying to upload my spreadsheet and getting all sorts of time out issues and whatnot... bah...

So the sheet is a fairly simple log. The main log sheet where "tickets" are entered has 10 columns, current order (doesn't need to be in this order, just have it this way): Date ticket was made, time, location of issue, type of request, assigned to, comments, Yes/No box if it is completed, who completed, date completed, time completed.

The Yes/No box has a VBA code attached to it that allows the row to be copied over to the "completed" tab so once it is marked as yes completed, it will transfer it to the other page. Likewise, if you are on the completed tab and find the issue isn't complete, you can change this to no and it will move it back to the log tab.

The idea is that completed tab is to be a running list of everything that has been done since the start of the sheet while the log tab will show all current open tickets. Both of these tabs look identical.

The above part was pretty easy to do, but what i am trying to put together is a cover page that can be sent out daily. This sheet would have a date entry on top that would prompt 2 sections to populate. The first section would show current tickets that have not been completed (just a copy and paste essentially of the log sheet) and the second section would show all tickets marked as complete on the day referenced at the top of the page. When i just did direct references to the log sheet, something i noticed is that when a ticket is marked as complete and the macro copies the line over to the completed tab then deletes the line off of the log tab, it then starts to give errors due to deleting lines. I'm guessing i should just do indirect references to make this work and that i can figure out. The part that i can't seem to grasp is the reference to the date to pull up orders completed on that date. A vlookup will show the first one on the list, but how do i get it to pull the 2nd, 3rd, etc... and have it go into the next row.

Hopefully that is drawing a better picture for you

Thanks a bunch for the replies!

 

[blueice2627]

Wrote Yes/No box, this isn't a box, it is just a cell with a data validation list to choose from.

Yes/No box if it is completed

 

[Zack Barresse]

So do you want to do this with formulas or VBA? Personally I'd use VBA but you can do this with formulas if you'd like, although imo it's not as elegant. Some follow-up questions:

• Can you tell us what columns houses the data? A:J? B:K?
• Headers on row 1, data starts on row 2?
• Which column can be used to determine the last row, is there one column which always contains data?
• Would "older" data be cleared out first? If so, please define exact range on Cover sheet data will go into (e.g. all columns from other sheets to go matching columns on Cover sheet, etc.).
• Should this be sent automatically? If so, how should it send, email? As PDF? As whole workbook?

When i just did direct references to the log sheet, something i noticed is that when a ticket is marked as complete and the macro copies the line over to the completed tab then deletes the line off of the log tab, it then starts to give errors due to deleting lines. I'm guessing i should just do indirect references to make this work and that i can figure out.

We'd need to see your code to fix it.

 

[blueice2627]

Hmm.. am i crazy or is there a way to add my file to the thread to make this easier? I don't see an "attach" button and dragging and dropping doesn't work... i seem to remember doing this in the past but eh...

To answer your questions:
Columns with data - All of them. The primary sheet(s) that house all the data is the log and completed. Here is a sample of what they look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Time
[/TD]
[TD]Location
[/TD]
[TD]Request
[/TD]
[TD]Assigned To
[/TD]
[TD]Completed By
[/TD]
[TD]Date Completed
[/TD]
[TD]Time Completed
[/TD]
[TD]Remarks
[/TD]
[TD]Completed?
[/TD]
[/TR]
[TR]
[TD]01/07/19
[/TD]
[TD]1:45 pm
[/TD]
[TD]Lobby
[/TD]
[TD]Broken Tile
[/TD]
[TD]Engineering
[/TD]
[TD]Me
[/TD]
[TD]01/07/19
[/TD]
[TD]2:30 PM
[/TD]
[TD]Tile replaced
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]01/07/19
[/TD]
[TD]2:30 pm
[/TD]
[TD]Restaurant
[/TD]
[TD]No hot water
[/TD]
[TD]Engineering
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Boiler went out
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]

So both of the above mentioned sheets look exactly like that. The log sheet will show all the "No's" under completed where the completed tab will show the Yes's
The "Cover Sheet" as I call it shows 2 sections like what i have below... the reason for splitting and condensing is to make it fit on one page to be emailed/printed out easier:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Completed Work Orders
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ongoing Work Orders
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date Completed
[/TD]
[TD]Time
[/TD]
[TD]Location
[/TD]
[TD]Request
[/TD]
[TD]Remarks
[/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD]Time
[/TD]
[TD]Location
[/TD]
[TD]Request
[/TD]
[TD]Assigned To
[/TD]
[/TR]
[TR]
[TD]01/07/19
[/TD]
[TD]1:45 PM
[/TD]
[TD]Lobby
[/TD]
[TD]Broken Tile
[/TD]
[TD]Tile Replaced
[/TD]
[TD][/TD]
[TD]01/07/19
[/TD]
[TD]2:30 PM
[/TD]
[TD]Restaurant
[/TD]
[TD]No Hot Water
[/TD]
[TD]Engineering
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So this page will split the 2 types of work orders (completed and not completed) and place them in their spot. Completed should be reflected by a date that is entered on the top of the page (show only the work orders that were completed on said day) and ongoing work orders should show all work orders that have not been completed yet (still open).

Headers - On the Log and Completed tabs, headers are on row one with data starting on row 2. For the cover sheet, Row one has a spot to enter the date, row 2 is left blank, row 3 has titles like above (completed work orders, ongoing work orders), row 4 has the table headers, and row 5 starts the list.

Last Row - In the current layout, Column A for date will always have something in it, but all columns will have data except for the columns regarding "completed" information since some items will be ongoing.

Older Data - Not sure what you mean by older data. Depending on the date at the top of the page, the completed side should update with items completed on that day. For the Ongoing Work Orders side, as long as the data is on the log sheet, it should appear here. Once a work order is marked as complete and transfers to completed (off of the Log tab), it shouldn't appear on that side of the cover page but it would show up on the completed side if the date is changed to match the date of completion (that made more sense in my head but reads confusing lol...)

Sending out - Doesn't need to be automatic, i can make them export to pdf and send out that way.

The code i used to transfer from the log to completed is below:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Target.Column = 10 And Target.Value = "No" Then
LrowCompleted = Sheets("Daily Log").Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & Target.Row & ":J" & Target.Row).Copy Sheets("Daily Log").Range("A" & LrowCompleted + 1)
Range("A" & Target.Row & ":J" & Target.Row).Delete xlShiftUp

End If
Application.EnableEvents = True
End Sub

I have this code on both sheets, the log and completed. This way if something is marked Yes under completed on accident, if you go to the completed sheet and change it back to "no" then it will move it back to the log. Since it is copying and pasting, then deleting the row, if the cover sheet has a direct reference like =IF(ISBLANK('Daily Log'!A3),"",'Daily Log'!A3), once row 3 gets deleted by marking that row as complete, then the formula gives a reference error like this =IF(ISBLANK('Daily Log'!#REF!),"",'Daily Log'!#REF!) since that row was deleted.