Assets booking sheet - tracking most recent booking

Hi All,

I have a problem I am trying to resolve.

I want to build a spreadsheet for managing the booking of 100 items. I want columns for item number, user, date booked out, date booked back in...

My question is;

I want to also be able to track the current status of any of the 100 item. So, if you imagine asset "8" has been booked out, back in, back out several times... there will be multiple occurrences of this on my sheet.

Currently, I have a "rolling"/"live" sheet which has the above columns and multiple occurrences... then I have another sheet which uses vlookups and returns the value against the latest date the item has been booked back in.

I am sure there is a much better way to achieve this result. Any suggestions?

Hi TommyTommyTommy.

Welcome to the forum.

Usually the most effective way to get help in the forum is to post a small Excel sample file that is representative of what you are working with, what you are attempting to achieve ... including a BEFORE section and an AFTER section (hand typed if necessary) often helps.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

This proposal is based on the premise that if you want to add new rows to Table 1, then the bookings will be arranged chronologically based on the Out column.
This proposal adds a column to Table 1 which is populated using: =COUNTIFS(A$2:A2,A2)=COUNTIFS([Item],[@Item])
The 'Who had it last' column on Table 2 is populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

This is somewhat similar to JeteMc's proposal. The formula is a bit longer, and it doesn't depend upon data chronologically sorted.

Formula:

Please Login or Register  to view this content.

I'll do my best.

I encourage learning to use the F9 function key and the Evaluated formula feature aka Fx. These are good tools for trouble shooting, analyzing and self instruction. I recommend using them whenever I can.

AGGREGATE is like the "Swiss Army Knife" of functions. It can perform 19 different functions referenced by numbers 1-19.
14 is the LARGE function.
The next value 6 is one of several options to ignore errors.
The 1 at the end is the k-value that returns the 1st largest ... i.e. max value from the array.

In the formula bar select just

(Table2[@Item]=Table1[Item])

and hit the F9 function key. You will see and array TRUE/FALSE. Applying any math operation to these will coerce them into their underlying numeric values of 1/0. Note that F9 presents results out of context.

Extend the selection in the formula bar to

Table1[Returned]/(Table2[@Item]=Table1[Item])

Hit F9 and you will see an array of 5 digit numbers and #DIV/0! errors. If you are not aware of it dates are numbers starting with day 1 at 1/1/1900. The formatting is cosmetic. Excel works with those 5 digit numbers and can regard them as dates.

Since the second option ... 6 ... directs AGGREGATE to ignore errors the 1st largest number is returned.

All of this is nested inside the MATCH function which I will assume you already understand. If not with the formula cell in question the active cell apply Formulas > Evaluate Formula. Repeatedly clicking the evaluate button Excel reveals step-by-step how results are calculated and does it in context.

Here is a link that explains AGGREGATE well.

https://www.myonlinetraininghub.com/...egate-function

Does this help?

Hi FlameRetired,

That is excellent I really appreciate it. Before I saw your reply, I did manage to figure out the "swiss army knife" of the "AGGREGATE" function - when I dived in and started deleting the "14", "6" it prompted the list showing the various options.

I will use the function key and evaluate formula going forward - really, give a man a fish / teach a man to fish. You've set me up. Thanks for that.

Couple things; F9 isn't working. Presumably it is a shortcut for the "FX" button. Instead it is resolving formulas as a value - e.g. if I put =SUM(5^2) it shows 25. If I select the cell and press F9 it removes the formula and puts 25 in it's place. I have a feeling you'll know what I am doing wrong?

Second thing; when I paste your formula into my sheet - it does not show the same information. A pity, I can't post the formula on here as I have a new account and it is considered code/links. The formula changes.

Three things.

The F9 is not a shortcut to Fx. It is a way to "cherry pick" elements of a formula ... remember out of context ... and display the results of that part only.
I forgot to mention that after F9 you should hit Escape. If you hit Enter it will commit that array as a constant. You don't want that.

Second thing; when I paste your formula into my sheet - it shows this instead after pressing the return key:
[FORMULA]Hi FlameRetired,

That sounds like you are selecting the whole formula "box". "[FORMULA]" part is the code tag that makes the "box" display.
Instead find a small (sometimes faint) and usually blue icon above the formula "box". Click that and it will select just the whole formula without fail. Copy that.

Originally Posted by FlameRetired

Three things.

The F9 is not a shortcut to Fx. It is a way to "cherry pick" elements of a formula ... remember out of context ... and display the results of that part only.
I forgot to mention that after F9 you should hit Escape. If you hit Enter it will commit that array as a constant. You don't want that.
That sounds like you are selecting the whole formula "box". "[FORMULA]" part is the code tag that makes the "box" display.
Instead find a small (sometimes faint) and usually blue icon above the formula "box". Click that and it will select just the whole formula without fail. Copy that.

I'm sorry - I've edited my post - where it said [FORMULA] that was me trying to use the post tags to try and circumvent this issue where it isn't letting me paste code because I am new to the forum. I have copied your formula beginning "=INDEX" and ending ",0))" per the formula you have posted!

I am pasting it to cell "B2" under the "lookups" sheet. And it is, for whatever reason, being modified after pressing enter.

New workbook attached showing the formula after copy/paste.

F9 - got it. It took a dummy exercise for me to grasp! I did "=SUM(1+2)*(5+2)" highlighted e.g (1+2) and lo and behold it shows "3", highlight (5+2) and I get "7" - I get it. Thanks - that is very useful to know.

I am pasting it to cell "B2" under the "lookups" sheet. And it is, for whatever reason, being modified after pressing enter.

Hmm. That's interesting. I've never noticed this behavior before. The Table2 references disappear.

The formula still works correctly at my end though. Apparently [@Item] is understood to be the current table.

Is this causing problems?

I think this question is different enough to start a new thread with appropriate title.