Formula/s for managing equipment hire

Hi all,

I'm trying to get a workbook together for a small equipment hire job I have taken on. What I want to do is split up the sheets so Sheet 1 = Equipment (so you fill out who hires what), then I want that data to auto transfer to Sheet 2 (only if it is filled out) so Sheet 2 = On Loan (Equipment that is hired), I also want to put in extra stuff here such as deposit and return date etc. Then on Sheet 3 = Equipment History, I want it to make a copy of completed Sheet 2 entries for a history log.

IS THIS POSSIBLE?!

And is it also possible so that when equipment has been returned, it clears on Sheet 1 (so that it is free for hire), but still keeps a record on sheet 3?Equipment Register.xlsx

I've been racking my anti-formula brain for the past few hours and can't seem to understand or make it work!

Please let me know if there are ways to make this happen/enquiries. Thank you!

ARRAY Formula in A3, then dragged across

Please Login or Register  to view this content.

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

Thanks guys, these are amazing! I'm so happy it works!
I have a few questions if possible:
1. I want to add in another column, for example "Equipment Type", how do I do this and include that in the formula?
2. How can I stretch this formula across many cells? We have about 200-300 pieces of equipment I want to register.

Again, thank you so much, this was amazing to get a reply to! THANK YOU

Assuming "Equipment Type" is added in Column G Of all sheets) then in G3


=IFERROR(INDEX(Equipment!G$3:G$20,SMALL(IF(Equipment!$D$3:$D$20<>"",ROW($D$3:$D$20),""),ROWS
($1:1))-ROW($D$3)+1),"")

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.

I assume equipment is ROWS not Columns: so extend range from 20 to 300 (or whatever) in the respective formulae.

Same formula can be dragged to the next column. Pl see file.

Thanks, I'm still struggling getting it to span over the cells, whenever I drag it the data seems to stop at 18?
Also I really wanted to get Equipment type in the First Column and move everything across one, is this a hassle? And the date seems to be going weird, now it's just 5 numbers?

I've attached what I've tried and miserably failed at if it helps!

You need to change dimensions from 20 to 100 (or whatever) in all of the formulae. and drag down 100 rows.


=IFERROR(INDEX(Equipment!A$3:A$20,SMALL(IF(Equipment!$A$3:$A$20<>"",ROW($A$3:$A$20),""),ROWS($1:1))-ROW($A$3)+1),"")

I don't understand comment about numbers: if you drag formula across you will get 0 in columns H to K as there is no data in "Equipment" for these columns,

Change the ARRAY formula like this
Range should be changed from G$3:G$20 as G$3:G$200 (all ranges)
=IFERROR(INDEX(Equipment!G$3:G$200,SMALL(IF(Equipment!$D$3:$D$200<>"",ROW($D$3:$D$200),""),ROWS($1:1))-ROW($D$3)+1),"")

I've changed the formula but when I drag it down the rest of the data disappears, and when I change the range in that cell, the data disappears again? Also with the date issue, I entered 26/06/15 in my Hire Date, and then in sheet 2 it appears as 42189 or something?

Are these issues because I'm on a MAC?

This layout attached is exactly what I want, however I just can't get it to 200 cells, it won't even show past 18 now! Ahhhh

Thank you that actually made so much sense! Now I just need to fix my hire date issues

Formula should be entered in ARRAY format.
How to enter the ARRAY formula I have given in my first post.
Once it is entered as ARRAY formula you will see Flower bracket {} auound the formula . Now drag it across.
Note that ARRAY formulas are different normal formula.

Thanks kvsrinivasamurthy i've fixed it now Do you have any idea about how to fix my date issues? I've inputed 26/06/15 in Sheet 1 and in Sheet 2 it comes up as 42181 - confusing?!

Format cell (column) as Date.

Thanks JohnTopley, it worked!

I have transferred over all my data, however I have one last issue! I wanted the data on sheet 2 to only appear when the 'On Loan to' section was filled out, however it appears on sheet 2 with 0 in the cell (because it is unfilled). How do i only get the data with the names to appear??

I have attached the spreadsheet for reference

Change to ...

In A3

=IFERROR(INDEX(Equipment!A$3:A$400,SMALL(IF((Equipment!$A$3:$A$400<>"")*(Equipment!$E$3:$E$400<>""),ROW($A$3:$A$400),""),ROWS($1:1))-ROW($A$3)+1),"")

Enter with Ctrl+ShifT+Enter

Copy across and down

Thanks again JohnTopely, everything is great! You wouldn't happen to know how I can organise the data on sheet 2 by name do you?