I am working with 2 separate spreadsheets within 1 workbook.
1st spreadsheet (room by room) 1 is a sheet containing several line items of data, and when their due date is (SEE COLUMN J) .
When we enter a PO number (COLUMN M) the date under column J becomes blank. there are multiple listings of the categories (COLUMN C) as the equipment is used in different rooms. Also note that there are "Options" (COLUMN B) where the due date (COLUMN J) is not shown as the quantity is 0 and therefore the due date is not needed
The second sheet (3AB Category) contains a pivot table that summarizes by category (COLUMN A) the information on the 1st spreadsheet.
BOTTOM LINE
I am trying to figure out a formula that can be added to the 2nd spreadsheet that will:
- Return the date from 1st spreadsheet for any category that does not have a PO.
- Also i need to have a date even if 1 line item has a PO and they other does not
- If there are 0 dates in column J (Room By Room ) for a single category, then the result on the 2nd spreadsheet should be blank
Keep in mind that i need this formula for the 2nd spreadsheet for each line item.
We need the pivot table to show the date if there are any items without purchase orders
