M
Monte Sliger
Hi,
I am using Excel 2000 to keep inventory records. Each inventory item has a
separate worksheet in the workbook. My columns for data are
A = Date of transaction
B = Description of transaction
C = Quantity in
D = Quantity out
E = Current quantity on hand
My data rows vary in amount for each sheet but none is larger than rows 4
through 1500.
Suppose my inventory sheet is named "ItemA". I have a recap sheet with the
Cell A1 being a cutoff date. The Item Name (in this case "ItemA") being in
Cell A4, the date needs to go in Cell B4, the description needs to go in
Cell C4 and the quantity in needs to go in Cell D4. I need a formula to
return the date, description and quantity in for the last row on the sheet
named "ItemA" which is on or before the cutoff date (Cell A1) and also has
an amount in the Quantity In column whether that is the last row on the
sheet or any row above the last one. I suppose I would need a separate
formula for each column to return the data for that corrresponding column on
the "ItemA" worksheet but if there is a better way to do it, that would be
great. I have tried using the LOOKUP function in each cell to return the
data, but I am obviously using the wrong parameters as I keep returning data
from the last row in the worksheet before the cutoff date regardless of
whether there is an amount in the Quantity In column or not.
Any help anyone can give me would be greatly appreciated.
Thanks,
Monte Sliger
I am using Excel 2000 to keep inventory records. Each inventory item has a
separate worksheet in the workbook. My columns for data are
A = Date of transaction
B = Description of transaction
C = Quantity in
D = Quantity out
E = Current quantity on hand
My data rows vary in amount for each sheet but none is larger than rows 4
through 1500.
Suppose my inventory sheet is named "ItemA". I have a recap sheet with the
Cell A1 being a cutoff date. The Item Name (in this case "ItemA") being in
Cell A4, the date needs to go in Cell B4, the description needs to go in
Cell C4 and the quantity in needs to go in Cell D4. I need a formula to
return the date, description and quantity in for the last row on the sheet
named "ItemA" which is on or before the cutoff date (Cell A1) and also has
an amount in the Quantity In column whether that is the last row on the
sheet or any row above the last one. I suppose I would need a separate
formula for each column to return the data for that corrresponding column on
the "ItemA" worksheet but if there is a better way to do it, that would be
great. I have tried using the LOOKUP function in each cell to return the
data, but I am obviously using the wrong parameters as I keep returning data
from the last row in the worksheet before the cutoff date regardless of
whether there is an amount in the Quantity In column or not.
Any help anyone can give me would be greatly appreciated.
Thanks,
Monte Sliger