There may be more information here than is needed, but I figure too
much is better than not enough!
I have multiple sheets in a workbook that keep track of files and
their status in a workflow.
Excel version is 2003
Background and Sheet Explanation:
The FolderLog tab keeps a list of all the jobs available, dates of any
status changes and identifies their current status.
The ReaderLog tab keeps a list of all the jobs in two separate tables
that were completed or parked.
The filename in column M on the FolderLogs tab and column A (for
completed) or column E (for parked) on the ReaderLogs tab is what
links the items together. When a job appears on the ReaderLog as
"completed", the completion date of the job is pulled over into the
FolderLog into Column V through a vlookup formula comparing the
Column Z is then updated with an if statement that identifies the
status of the folder depending on what fields have dates entered into
them using the following formula:
My problem lies in that a folder with the exact same filename can
potentially be sent through the system twice, if that happens, my
vlookup formulas are going to retrieve the previous statuses since
they抣l be the first match, and jobs that are in progress will show
completed even though they haven抰 been yet.
The good news is that once a job is completed, I don抰 need to keep
the information separately on the ReaderLog tab, and I can remove
it. In order to remove it though, I need to update all row items on
the FolderLog tab that have a status of 慶ompleted?to remove the
The best I can come up with is needing to use a vb macro where if the
status column is calculating that the status in column Z =
揅ompleted? the macro will select the entire row and copy/paste-
special to replace the formula with current values. After completing
this for the entire FolderLog tab, it should then go to the readerlog
tab and delete items that are completed (if column c = completed,
delete values in columns a& b AND if column J = completed, delete
values in columns E,F,G,H &I)
Alternatively, the macro might be also able to say if the calculation
in the date completed column (U) returns a value that is not blank,
(follow the same set of actions as above to replace the formulas with
values and delete values in the other sheet)
Thoughts? Ideas? I'm stumped.