I am creating an inventory matrix, which has on the first column a list of all the items in the inventory, and on the first row a list of the dates for when the inventory gets renewed. Basically, not all items get renewed all at once and this is the problem.
The cells in the middle of the matrix (basically the intersection between item and date) are the number of items that have been purchased on the specific day.
I have been able to create with the value lookup function an index that tells me how many items have been most recently purchased, combining value lookup, the max function (to order the max. date) and match to combine the column and the rows.

The problem is that like this, I only see the number of items that have been purchased on the last round of shopping, even if this number is zero. I now have to determine (in another sheet) when was the last time that a specific item has been purchased. With the solution above mentioned, I can see the amount purchased during the last time but this does not help now because not all items are purchased at once, and therefore some items are not being purchased during the last shopping spree. I want a function that tells me the latest day (therefore looking in the row of the dates), the number of items (therefore looking in the center of the matrix) has been different from zero.

For instance:

  • I have in my inventory cucumbers and tomatoes.
  • I purchase on the 01.07 3 cucumbers and 2 tomatoes.
  • The 08.09 I purchase 0 cucumbers and 3 tomatoes.
  • I am only able to write a function that will tell me that the inventory for cucumbers have been last renewed the 08.09 with 0 cucumbers, while I actually want a function that tells me the date 01.07, as it's the last time cucumbers have been purchased.

Here are screenshots of the three sheets I am working with: https://drive.google.com/folderview?id=0BwMAvO1prgOIfkpxWEtxeTRPT2ZfdXhYRTZrVTlMZUlvOVMzRW5qdUxPclhYYXlnSFBYVlk&usp=sharing

n°1 is the grocery list, which relates every items and the dates of the last purchase. in the intersected cells are the number of which are bought each time. sometimes they can also be zero, as not every item is purchased every time.

n°2 is the storage sheet, which connects to the grocery list. I used VlookUP to determine how many for each item have been purchased during the most recent purchase.

what I am trying to do in n°3 is under "last renewed" to have the date the specific item (which shows in the button, such as "olive oil") has been purchased the last time, which is different from the last time of the purchase as some items are not being purchased all the time, and therefore for them, although no items have been purchased (therefore #item = 0), it would still show that they have been most recently purchased in that specific date. (see example above). --> how do I do it?

I hope this is clear, it is pretty messed up in my head.
thanks in advance.

  • 1
    well, sorry its not really clear. Can you post and actual table layout of your spreadsheet? Also, this issue, from what I understand of it, is perfectly designed for a database. In fact, all inventories should be maintained in a database. But lets see your data structure in a table. – htm11h Aug 4 '15 at 20:54
  • Ok, so: drive.google.com/… in this folder there are three pictures of the three sheets I have so far. the n°1 is the grocery lists, that puts in a matrix the n° of items purchased each time. the n°2 is the storage list, and vlookup was used to put determine how many of which item were purchased for the last time of grocery shopping. as you can see, some of them say 0 because the last grocery shopping the items purchased were zero. – Lara Aug 4 '15 at 21:15
  • the n°3 is a table I created with a macro and the form function, basically you have the list "compressed" so that you can see each item individually. in the "last renewed", I want the function that I am looking for: the last time in the grocery list an item has been purchased. so, instead of telling me the last time there was a grocery shopping (which is what it tells me now) I want to know when was the last time the number of the specific item purchased was different from zero. – Lara Aug 4 '15 at 21:17
  • You said you have a macro on the third sheet. Can you post that? – DiegoAndresJAY Aug 5 '15 at 1:23
  • It's not properly a macro, in n°3 you see there is a little button with "olive oil" and if you click on it you can actually select any of the items written in the matrix. – Lara Aug 5 '15 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.