On my transactions sheet I have a massive list of product transaction (such as stock movements and orders). In this list, the same product can appear hundreds of times, but not every column has data in it all the time. As an example, when an order is processed, the "stock quantity" column is left blank, since that particular transaction doesn't care how many are in stock, it instead puts a -1 in the "orders" column. My question:
Obviously, if i do a lookup for a particular product it is going to find the first instance where the product code matches the one i'm looking for and return that value and sometimes that will be a blank. I'm currently using a lookup that looks UP the transaction list so that it catches the last and most recent data first...
I have no problem in using some function other than the lookup, so long as it isn't going to cause an issue if I have 50+ cells running the same formula when a product code is entered into B28, and the result can be a number or text.
Please Login or Register to view this content.
In pseudo-code, my formula would do this:
I hope my description is useful, and thanks in advance for the help I will receive=
if cell 28 is blank, leave my cell blank otherwise
look from the bottom of the transactions list, column A, for a match to B28 and
when a match is found, check for a value >0 in column F
if the cell is blank, keep moving up the rows until you find a >0 value otherwise
give the value in column F