I am doing this in 2010 right now, however, the system I need this one only has 2007. Not sure if this makes a big difference.
I am trying to make a spreadsheet for each department that feeds into a coordination process. When a request is received, I log it in A1 (or whatever the next row is). I assign it a file name or keep the one that comes in. I put in the area that is effected and who the request came from in the appropriate fields. Column F thru K are dates that the packet goes to each department for review. L is notes, M is the name of the project, N is the date it is saved in shared drive upon everything completed, O is whether the request is new or an old file missing data, P is formulated as [=IF(K2>1/1/2001,"CLOSED","OPEN")] to let me know if the file is open or closed, Q is formulated as [=IF(P2="OPEN",IF(K2>1/1/2001,"SENT",IF(J2>1/1/2001,"ADMIN",IF(I2>1/1/2001,"BIG BOSS",IF(H2>1/1/2001,"BOSS",IF(G2>1/1/2001,"2ND REVIEW",IF(F2>1/1/2001,"1st REVIEW",IF(E2>1/1/2001,"RECEIVED"))))))),"COMPLETED")] to let me know via a quick look at where the packet is at.
What I am trying to do now is several things:
Question 1: The formulas currently in P and Q only work properly once I add data to the row then copy the cell above the formula to insert in the next row. If I preformat the entire worksheet, the formulated columns give error codes or False listings until I put data in the rest of the row. I would like to preformat the entire columns so no one messes up the formulas for these columns, so all anyone has to do is put data in each row without having to drag the previous rows formulas. How do I do that and did I make any sense explaining it?
Question 2: This applies to the spreadsheets “Staff 1-3”. When I create a new entry and the status is listed as open, I would like to have a formula that copies the new entry onto the spreadsheet “Consolidated”. For example: I just made row 2, I would like row 2 to be copied into the next available row on worksheet “Consolidated”. How do I do that?
Question 3: This applies to the spreadsheets “Staff 1-3”. Once a row is closed, I would like that row to be removed from the top of the sheet to below the completed line at row 22. How do I do that?
Question 4: This applies to all sheets. Once a row is completed on the “staff” sheets and moved to below the completed line, I would like a formula to remove the row from the “consolidated” sheet permanently. How do I do this?
Question 5: This applies to all sheets. Once a date is filled in (Column F – K) I would like to make the fill for that cell turn Yellow. Once the next cell date is filled in remove the yellow and apply to the next cell. How do I do that?
Question 6: This applies to all sheets. Same as above, but for any row that has pending in it to turn the cell fill orange. How do I do that?
I have uploaded an example spreadhseet. I know its a lot, but I am trying to streamline our processes. Any help would be greatly appreciated.
Bookmarks