+ Reply to Thread
Results 1 to 3 of 3

Multiple Spreadsheet Interactive Formulas

  1. #1
    Registered User
    Join Date
    01-10-2013
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Multiple Spreadsheet Interactive Formulas

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Multiple Spreadsheet Interactive Formulas

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.


    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    I know its a lot, but... --> It's actually too much. Try asking one or two questions at a time (per post)
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-10-2013
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple Spreadsheet Interactive Formulas

    Thanks. Will do.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1