Pulling specific data from one sheet to another sheet based on status changes

[kchick118]

Hi All,
First off, thank you in advance to anyone who takes the time to review this query.

I am hoping to receive some help on how to pull data from one master spreadsheet to another in one workbook based on the status of each item.

In the workbook, Sheet2 I am considering the "master" - the table that will be manually filled in.
Sheet1 is the spreadsheet I am hoping to pull data from Sheet2. I am hoping that when an item in Sheet2 is added with an ECD, it is automatically placed into the "Current" table on Sheet1 with the specified data highlighted/color coded. When an item is completed and a date is inputted into Column M, the row with the line item is moved from the "current" section in Sheet1 to the "completed" section with the specific data labeled/color coded.

The idea is to have all of Sheet1 be autopopulated, and only working on Sheet2.

Is something like this possible?? If this solution is possible without changing the macros that would be best, but obviously am open to any and all possible solutions!

Thank you in advance and let me know if more detail is required before resolving.

[Ochimus]

Something on these lines? If you change the ECD (Col K), macro adds the relevant cells to next free row in Current Section of Sheet1.
If you enter a Completion Date in Col M, it finds the relevant record on Sheet1 in Current and moves it to the next free row in Completed section.
This assumes you won't "skip" the ECD step, or there will be nothing in Sheet 1 to update!

Please Login or Register  to view this content.

[kchick118]

Hi Ochimus,
I really appreciate your help here and taking the time to look into this issue.
I unfortunately am not having luck with that macro. After refreshing the page the macro didn't take to Sheet 1.
It is possible I may be looking at it incorrectly -- do you have any suggestions?

Thank you again!

[JeteMc]

Perhaps the following formula based proposal will help.
1. Select cells B3:F17 on Sheet1 and change the formatting from text to general.
2. Populate the top table using:

Please Login or Register  to view this content.

3. Populate the bottom table using:

Please Login or Register  to view this content.

Note that the column header in cell E2 needs to be changed from ECD to ECD Changes.
Note that the formatting of cells E14:E17 needs to be changed to date.
Let us know if you have any questions.

[kchick118]

Hi! Thank you so much for taking the time to help with this one, this is definitely what I was looking/hoping for!

One quick bug that I am looking to fix with the formulas. It seems the only way the data from Sheet2 is pulled onto Sheet1 with this formula is if there is data in Column K. I tried revising the formula to exclude column K and replace with Column J instead, but am not having any luck. I would like for Sheet1 to pull all of the data from Sheet2 depending on whether it's complete or current.

Do you have any suggestions on how to fix?

Thank you so much again!

[JeteMc]

Column J on Sheet2 of the file attached to post #1 has the values Late, At Risk and Low so I am confused as to how complete and current relate.
Are we possibly talking about the Open and Closed values in column I?

[kchick118]

Whoops! I figured it out! I edited the "ISNUMBER" columns. Thank you again this was SO helpful!!!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.