Equipment Schedule

[crburke92]

I've been looking at a few different ways of doing this, hoping someone could possibly shed some light on what may be the easiest way. I'm trying to build a schedule for my offices equipment that goes out into the field (We have about 70 or so pieces of gear to log). Gear to be listed down the rows, dates to be the columns.

Sheet1 to be the schedule
Sheet2 to be a form you fill in

So sheet2, someone fills in the blanks. Gear to be used(dropdowns maybe, to make the reference to sheet1 easier?) start and end dates, and then maybe some text that fills in for a job number or comments. When a button is pushed, it finds the intersect point on sheet1 between the selected dates and the gear used, highlights the cells and inserts the comments into the first cell, then clears the information filled in on sheet2 so it can be filled in again.

Not sure if there's an easier way to do this through formatting or VBA, any help is much appreciated

 

[mumps]

A macro would be able to do this for you but without something to work on, it's difficult to help. Perhaps you could design a workbook with the 2 sheets formatted the way you described and enter some data (gear and dates in sheet1). Then you could post a screen shot of what your data looks like in both sheets? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[crburke92]

I have the Mr.Excel HTML Maker downloaded and the add-in on my ribbon, but when I select my custom range to print and hit ok, nothing ends up happening.

 

[mumps]

Can you upload to www.box.com. or www.dropbox.com?

 

[crburke92]

https://www.dropbox.com/preview/Public/Test_Schedule - Rev1.xlsx?role=personal

This work?

 

[mumps]

We're almost there! The link you posted takes me to the DropBox home page. I need the direct link to the file. Mark the file for sharing and you will be given a link to the file. Copy/paste the link here.

 

[crburke92]

https://www.dropbox.com/s/cp5hokpchrqmz8d/Test_Schedule - Rev1.xlsx?dl=0

I'm apparently bad with dropbox haha, my apologies

 

[mumps]

Thank you for the file. In Sheet2 you have chosen all the products listed in Sheet1 which means that all the cells for all the products between Jan. 1 and Jan. 5 would be highlighted. Is this correct? Also, will all the values in column B of Sheet1 always be unique, in other words no duplicates?

 

[crburke92]

For sheet1, yes, those will always be unique. I split the equipment name and its "EQ ID" because it will likely be easier to reference just that number as opposed to the entire name. Also yes for sheet2, the idea is to have an easy way of selecting all of the gear required and the dates required to then transfer over to sheet1.

I've been reading more and more into VBA and have made some pretty effective sheets for my company, but my background is not code so its usually messy/takes me awhile to get there haha. Its been fun though!

 

[mumps]

Click here to download your file. I have added drop down lists in Sheet2. Just click the button.