Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Formulas then Delete Cells based on conditions - XL2003
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 filename: =IF(ISNA(VLOOKUP(FolderLogs!M2,'ReaderLogs'!A:B,2, 0)),"",IF(VLOOKUP (FolderLogs!M2,'ReaderLogs'!A:B,2,0)=0,"",VLOOKUP( FolderLogs! M2,'ReaderLogs'!A:B,2,0))) 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: =IF(A2="","",IF(U2<"","Completed",IF(Y2<"","Disp atched",IF (V2<"","Parked",IF(K2<"","Dispatched",IF (J2<"","Created","Pending")))))) ---------- Issue: ---------- 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 theyll be the first match, and jobs that are in progress will show completed even though they havent been yet. The good news is that once a job is completed, I dont 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 completed to remove the formulas. 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 = Completed, 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove Formulas then Delete Cells based on conditions - XL2003
I wrote 2 seperate macros to make it easier to get the code working. Yo
always can combine them after you tested the code. Sub ReplaceFolderLogs() With Sheets("FolderLog") Set c = .Columns("Z").Find(what:="Completed", _ lookat:=xlWhole, LookIn:=xlValues) If c Is Nothing Then MsgBox ("No completed items found") Else FirstAddr = c.Address Do c.EntireRow.Copy c.EntireRow.PasteSpecial _ Paste:=xlPasteValues Set c = .Columns("Z").FindNext(after:=c) Loop While Not c Is Nothing And c.Address < FirstAddr End If End With End Sub Sub Replacereaderlog() With Sheets("readerlogg") LastRow = .Cells.SpecialCells(xlCellTypeLastCell) For RowCount = 1 To LastRow .Range("A" & RowCount).ClearContents .Range("B" & RowCount).ClearContents If UCase(.Range("J" & RowCount)) = "COMPLETED" Then .Range("E" & RowCount).ClearContents .Range("F" & RowCount).ClearContents .Range("G" & RowCount).ClearContents .Range("H" & RowCount).ClearContents .Range("I" & RowCount).ClearContents End If Next RowCount End With End Sub "bambier" wrote: 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 filename: =IF(ISNA(VLOOKUP(FolderLogs!M2,'ReaderLogs'!A:B,2, 0)),"",IF(VLOOKUP (FolderLogs!M2,'ReaderLogs'!A:B,2,0)=0,"",VLOOKUP( FolderLogs! M2,'ReaderLogs'!A:B,2,0))) 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: =IF(A2="","",IF(U2<"","Completed",IF(Y2<"","Disp atched",IF (V2<"","Parked",IF(K2<"","Dispatched",IF (J2<"","Created","Pending")))))) ---------- Issue: ---------- 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 theyll be the first match, and jobs that are in progress will show completed even though they havent been yet. The good news is that once a job is completed, I dont 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 completed to remove the formulas. 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 = Completed, 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight cells based on conditions | Excel Discussion (Misc queries) | |||
How to sum cells based on conditions | Excel Worksheet Functions | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
Validate Cells based on if conditions | Excel Programming |