Question description:
This user has given permission to use the problem statement for this
blog.
trying to copy over a name and primary date from one row of sheet 1 to sheet 2 if it meets the following criteria: a secondary date field located on sheet 1 is left blank for 15 days or more beyond the primary date field. Sheet 2 is the list of everyone who still does not have the secondary date field fulfilled, but the names and original dates only populate if the secondary date field is not fulfilled after 15 days from the primary date.
Solved by F. B. in 13 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
30/01/2018 - 07:07
Hello :)
User
30/01/2018 - 07:08
hello!
Excelchat Expert
30/01/2018 - 07:08
Can you explain the criteria a little further?
User
30/01/2018 - 07:09
certainly.
User
30/01/2018 - 07:11
I have a list of names that will need two things verified. The date of the person's evaluation and the date that the person's evaluation was approved.
Approval of the evaluation should happen within 15 days of the evaluation, but in the event that the approval is not found, I want a second sheet in the workbook to populate all the names and original evaluation dates that have been outstanding for more than 15 days
User
30/01/2018 - 07:11
I'm sure there
User
30/01/2018 - 07:12
will be some type of lookup and edate functions used, but that's about where I'm stuck... because the function needs to pull data over that is left blank for more than 15 days past the original date, I'm stuck finding the right formulas to make this happen
Excelchat Expert
30/01/2018 - 07:13
Okay, so if the cell that is looking for the 15-day test is going to be filled in, then you will need something other than formulas.
Excelchat Expert
30/01/2018 - 07:14
It almost sounds like you will need to use VBA (custom code) to solve this.
User
30/01/2018 - 07:14
For example, John and Jane Doe have had evaluations done more than 15 days ago but do not have the MD signed date. The MD signed date is left blank because it hasnt happened yet, so their names and the original eval date should be populated in sheet 2
Excelchat Expert
30/01/2018 - 07:14
Okay, what happens once they have MD signed?
Excelchat Expert
30/01/2018 - 07:15
Oh, wait, nevermind.
User
30/01/2018 - 07:15
Once they have MD signed date populated, they no longer meet the search criteria and are removed from the list on sheet 2
Excelchat Expert
30/01/2018 - 07:15
Since we are talking about comparing dates, that can be done by formula.
User
30/01/2018 - 07:15
even if the compared date is just a blank?
Excelchat Expert
30/01/2018 - 07:15
That can be part of the formula.
User
30/01/2018 - 07:15
ok
Excelchat Expert
30/01/2018 - 07:16
So no matter what happens (even if date is >15 days), once MD signed has a date, the record is removed from Sheet1.
User
30/01/2018 - 07:17
removed from sheet 2 where all the incomplete signatures > 15 days are populated. yes
Excelchat Expert
30/01/2018 - 07:17
Okay, what happens on Sheet1?
Excelchat Expert
30/01/2018 - 07:17
imput data?
Excelchat Expert
30/01/2018 - 07:17
input
Excelchat Expert
30/01/2018 - 07:17
*
User
30/01/2018 - 07:18
sheet 1 is input data that will be kept for internal records, the meaty part of the data
Excelchat Expert
30/01/2018 - 07:18
I see.
Excelchat Expert
30/01/2018 - 07:18
What is the max number of records you will have?
User
30/01/2018 - 07:18
500?
Excelchat Expert
30/01/2018 - 07:19
Okay.
Excelchat Expert
30/01/2018 - 07:19
If you don't want to have any spaces in the data, we will still have to solve this using VBA which will require a new session using the VBA/Macro problem description. Is that okay>
Excelchat Expert
30/01/2018 - 07:19
?
User
30/01/2018 - 07:20
that is alright. can we do that?
Excelchat Expert
30/01/2018 - 07:20
Yes, absolutely. Please end this session and then repost with VBA/Macro as the question type.
Excelchat Expert
30/01/2018 - 07:20
I'll start working on the code so that we don't lose any time.
Excelchat Expert
30/01/2018 - 07:20
thank you :)
User
30/01/2018 - 07:20
ok thank you!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
