-Excel based equipment and sites list that is linked to an access database. Monthly we run a query in the database that populates an equipment testing list for the current month. This is merged to the left-over items to create our testing list.
-This testing list gets uploaded to google sheets and color coded based on the type of equipment is in the row. Google sheets has been used in the past because it gave real-time access to the physicists on the road and the back-office staff simultaneously. Additionally, it can be accessed through mobile or PC. Columns are added to the list so that information can be collected as the equipment goes through its testing process and the reports get sent to our clients. These items include but are not limited to tested date, submitted dated, completed date, corrective action, etc.
-In google sheets two more sheets are added to the file, one for “tested not completed” and one for completed. As the field staff test equipment, the items get moved to the “tested not completed” sheet where they are managed by our administrative staff until the report is submitted to the client. Once submitted to the client, the item is moved to the completed list. In a perfect world, all items on the testing list (populated by the database) flow through the “tested not completed” sheet and to the completed sheet so that at the end of the month, the testing list would be empty and the completed list would be full, in which time the next month would be run and the process would start over.
We are thinking of switching all these items into Microsoft since we use Microsoft for everything else. With the new functionality of SharePoint, one drive, mobile excel, etc we think this could work but have some reservations on how the automation would work because it seems that the cloud based Microsoft excel does not have the functionality of macro based excel files (maybe this could be done with Power Automate?). Would like to discuss our options here and make the best decision to move forward with the project.
Creating buttons (in button bar at the top of each sheet) to move the items from sheet to sheet. (See examples attached):
Button 1 – “Move Tested” located on “Testing List” this will move rows to the “tested not completed” sheet if they have initials in the Tester column and a value (date) in the date tested column.
Button 2 – “Move Completed” – located on the “Tested Not Completed” sheet. This button will move rows to the “completed” sheet if they have a value (date) in the sent to client column.
Button 3 – “Sort by oldest” located on “Tested Not Completed” sheet. This button will sort the sheet from the oldest to the newest based on the “Date Tested” column
Button 4 – “Sort by facility” located on the “Tested Not Completed” sheet. This button will sort the sheet in alphabetical order based on the facility code column.
Button 5 – “Sort by tester” – located on “Tested Not Completed” sheet. This button will sort the sheet in alphabetical order based on the Tester column.
Button 6 – “Email Reminders” – located on “Tested Not Completed” sheet. This button will email a script email for all reports that have not been submitted in 3 business days. The emails will be sent to a different group of people based on the initials that are in the tester column. One email should go out for all the overdue items for a single tester and it should include the facility, unit and frequency data for each in the email. See attachment for a sample email.
Button 7 – “Way overdue alert” – located on “Tested Not Completed” sheet. Similar to the email above but to a different group of people and different amount of business days.
Button 8 – “Add Item” – Located on the “Testing List” sheet. Initiates a form that you fill in with each column as an entry. When submitted this item gets added to the “Testing List” and is placed in the row at the top of the bottom with the other items from that facility.
Bu iş için 14 freelancer ortalamada $18/saat teklif veriyor
I am expert in Data entry, Excel and in advance also, this will help me out to make a good project for you with minimal errors. I can assure you that you will get this project within the time Thenkyou
I sugest, for your project, the use of VBA. It's easiest to program what you need, and could be completed in 6 hours. You'll not have any problem to use the VBA, once Excel in desktop is connected with Microsoft Cloud Daha Fazla
I am interested to do your project.I have 4 years of experience in creating VBA macro for this kind activity. I will deliver the project on time.