Date compilations

[David]

I am tracking the progress of a construction project on a
spreadsheet. The columns are named with the various tasks
that have to be performed in each room of the building
(e.g. framing, electrical, mechanical, painting etc.), and
the rows contain the room numbers in the building. Each
week the building is inspected and the result of the
inspection is recorded in a new workbook which contains
the date of the inspection (cell P1). When a task is
completed for a room, a checkmark is placed in the
appropriate cell. After finishing the inspection and
updating the workbook, the file is Saved, and then Save
As... to create the workbook that will be used at the
following week's inspection. When the project is finished,
all of the cells of the last workbook (last inspection)
will have checkmarks.

After the project is finished, I want to create
a 'Completion Date' workbook for the project, placing the
date of completion of each task for each room into the
corresponding cell of the worksheet. (If it makes it
easier, I can copy all of the workbooks onto separate
worksheets of one workbook.)

How do I create the Completion Date workbook?

Thanks, in advance...

 

[Jim]

How do I create the Completion Date workbook?>
The same method used in eating an elephant...one bite at a time, <g>

Maybe you would get more responses if you descibed a single SPECIFIC goal,
state what you have tried, and the problems you encountered.

 

[Guest]

Fair comment.

Example:
cell G12 of all the workbooks contains the state of
completion of the painting in room 412. When room 412 was
inspected on Nov 7, it was determined that the painting
was completed and a checkmark was placed in cell G12. (All
subsequent workbooks from Nov 7 on also contain a
checkmark in cell G12.)
The formula in cell G12 of the Completion Date workbook
should return a result of "Nov 7" by reviewing all the
workbooks and determining that a checkmark first appeared
in cell G12 in the Nov 7 workbook.

The formula to accomplish this eludes me.

I hope this provides a clearer explanation of my goal.

 

[Jim]

Well, it is still not entirely clear, but maybe this is what you need:

=MAX(Sheet1:Sheet10!G12)

This will return the max serial date (you may Format>Cell>Number tab to your
date format) for the latest date. Be sure to change the sheet references as
needed. Sheet1 is on the left and Sheet10 is on the right (US).