• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Populate by Time

P

Patrick Spence

New Member
I am trying to make some cells auto populate on the Event Log tab depending on what is on the second tab. This first time is meant to be a quick overview of what is going on while the second tab is going to be more detailed. What I would like to do it have the box on the Event Log tab auto fill in with the comments depending on the time for each event. My problem is the time won't always be the exact same time. If they are not the same time I want it to include the information from the closest time from before the one list on the Event Tab. So if the Event Tab times shows 5:30 it needs to return "Comment 4" not "Comment 5".

Thank you in advance for any help you can give me on this.
 

Attachments

Hi Patrick

This can be achieved with the a formula however I observe some problems with the formatting in 'Report Template'.

For starters, the time value in 'Event Log' is a genuine serial time value. The time values however in 'Report Template' are not - they are text values. I assume your choice of format here is driven by your need to enforce valid time entries in the template. The variations of formats between the two sheets invariably causes problems because the data types do not match and in order to compare one value to the other to ascertain a match (albeit an approximate match) both data types are required to be the same.

So:
  1. Remove the validation rule in 'Report Template'!G:G
  2. Click on G1, and then highlight the entire of column G
  3. Go to Data > Text To Columns, choose 'Delimited' and then hit 'Finish'
  4. Format the range as h:mm;@
  5. Apply a new validation rule to the time input, and the formula for the rule should be as follows: =(G2<1)*(G2>0)
  6. Go to 'Event Log'!D9 and enter the following formula: =IF(($B8-VLOOKUP($B8,ReportTemplate!$G:$G,1,TRUE))<(--"00:30:00"),INDEX(ReportTemplate!$A:$A,MATCH($B8,ReportTemplate!$G:$G,1),1),"")
  7. Copy this formula (see 6.) into the other comment cells.

And one final observation: I note that your times start at 4am. You appear to continue beyond 4am again in your table below from rows 187 onward. I suspect these rows should be removed.

Regards
Jon
 
You must log in or register to reply here.
Back
Top