Continuously moving inventory - keeping a record without overwriting current information

Hi there,

We have an existing Inventory database in Excel that i have imported successfully. However now I need to figure out - if I can - how to 'track' the movements of any given piece of equipment.

For example we have a Computer Monitors that are 'loaned' to customers every so often. I woud like to keep a 'log' of the movements of each monitor - tracked by serial number and description.

I can add this in as a column, 'On Loan to', Loan Start Date, Loan End date etc, however when that item is returned, the loan agreement period is complete and it is back in OUR warehouse, I can only 'delete' the information and start again... can I keep a log (without dupplicating the record to confuse everyone or deleting the record all together from the master list, of who has had each line of equipment and for how long etc as per my columns. Then from THAT list I could sort/report on products that ar eloaned frequently for X purpose, again as per my columns.

Thanks! Hope that made some kind os sense...

Find more posts tagged with

Manufacturing

Sheets

Product Development

Conditional Formatting

Project Management

Comments

Amye

Excuse the horribly written babble. In the meantime I have utilised the heirachy indenting for this purpose, I simply copy and 'paste special' below the item row and make that row a child row - of which i can roll up and down to view previous loans.

This will work for now, but I forsee a few long term issues with this - and data being duplicated or overwitten by accident.

Appreciate any feedback

Amy Johnson

You could use a report. Keep all your records in a sheet then use a report to see only the rows you need to see. For example a report of off the items that are loaned out. Or a report for the loan history of x item. Or all the items loaned out in x timeframe.

Reports are powerful and can pull data from sheets based on almost any aspect of the data.

http://help.smartsheet.com/customer/en/portal/articles/522214-creating-reports

Amye

Hi Amy,

Thanks, I am a little confused - I have looked at reports a little but maybe not deep enough to understand. I 'get' that I can run the report to view the loan history based on my practice - as below.

How would I capture the 'history' in the first place. When an item is marked as ON LOAN, conditional formatting starts a chain of events that prompt the user to complete cells/information based on their inputs. Hence I can see a current status of what is ON LOAN and AVAILABLE.

Then, when that same 'row/product' is AVAILABLE again - upon return - conditional formatting drops off and I am then left with a row filled with details that are no longer current. At the moment I am copying that row, pasting below that same row and creating a heirachy with the copied line that I can then 'roll up' the information and manually deleting the ON LOAN information that was populated for the now AVAILABLE product - this enables me to 'keep the data' that I need as a history but still show our inventory in a live state - handy for our guys on the road..

Is there a better way to capture this continuous history? Or are you meaning that I duplicate rows each time a product goes out and comes back in and then run the report to 'narrow down' what I see. Discussionsare something I looked at but with start and end dates and conditional formatting and for reminders/alerts based on these dates etc this wasn't viable.

Happy to hear any suggestions.