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.
- 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.