Excel for equipment reservation

[prashr]

Hi

I have a requirement for my very small business where I rent out equipment. I dont have the budget to buy a tool hence want to maintain the inventory in a excel.

Here is my requirement.

Say my devices are names screw, nuts, bolts, plug, paint, hammer

My users are A, B,C,D,E,F,G,H .

I want to Track which user has booked for which date on a current month and another user should not be able to book the same device on the same day.

Please help.

Thanks in Advance
Prashant R

[akhileshgs]

Hi Prashant,
can u please upload a sample file?
We can insert the needed formula in that and give you. If we make our sheet, you may not be able to mirror it in your tracker

[prashr]

Hi

Thanks for the prompt response.

Sample file attached. Kindly assist.

Thanks in advance

Prashant R

[Bhuvi]

Hi both,

I have a similar kind of sheet already with me, in this you maintain the user and tools data in tools tab and enter the records for each day with help of a drop down.
Wherever more than one tools are booked in a date it would color it as red.
A macro button on tab month helps add new months.

Hope this helps !

[akhileshgs]

Wow... Thanks.... I was really cracking my head to get it done...

Sent from my Nexus 4 using Tapatalk

[oeldere]

I should solve this problem differant:

Make just 1 worksheet for all data.

It is made with the follow columns:

date / user / list

Then you have all data on 1 worksheet, whch made it much easier to analyze the data (e.g. with an pivot table).

[prashr]

Thanks a lot Bhuvi. Exactly what I was looking for. No words. I am surprised I got this without paying a penny.

Just curious to know if there is a way I can know what all equipment is available for booking on a particular day once some data is populated?
2. I see the data validation for equipment is done from the "Data" worksheet and the source name is "=tools" . Wherease in "Data" worksheet the name is "Lists". How is this done?

Hi both,

I have a similar kind of sheet already with me, in this you maintain the user and tools data in tools tab and enter the records for each day with help of a drop down.
Wherever more than one tools are booked in a date it would color it as red.
A macro button on tab month helps add new months.

Hope this helps !

[prashr]

Hi

Thanks for the reply. Date is a rangeless field. Can you make a sample sheet ?

[Bhuvi]

2. I see the data validation for equipment is done from the "Data" worksheet and the source name is "=tools" . Wherease in "Data" worksheet the name is "Lists". How is this done?

two Dynamic name ranges are created as "tools" and "users" using name manager. You can edit and change the names if you wish to.

Just curious to know if there is a way I can know what all equipment is available for booking on a particular day once some data is populated?

It's possible, but I have to find a method of doing this, would let you know if I crack this.. anyone else can also help pls

[prashr]

I found this online link really helpful. Whats the use, Im not able to duplicate it for my scenario

http://www.contextures.com/xlDataVal03.html#Depend

[atrinh]

Hi Bhuvi,

I know this post is about 3 years old but I have been searching for a template like the tool_tracker.xlsm you posted. I was wondering if the sheet is capable of booking equipment beyond row 34? I may have up to 100 bookings for one month and wonder if this template you created will still work for me.

Thanks in advance,

Anton