How do I auto populate a cell from a searched result

[warlordlovejoy]

Hi All,

I have a time schedule that auto populates when you change the date from a list of jobs on a separate tab. I currently have it setup to display the word "allocated" but ideally I want 3/4 of the fields from that job to populate the cell/s. At the same time, ideally I would like it to change colour depending on the status (as in the jobs tab) but also for those cells to auto merge and then unmerge when the date is changed.

I know this is a lot to ask but if some one can help with some or all of it I would be greatly thankful. I'm officially stuck!!!!!!!!

Drivers Schedule.xlsx

Many Thanks

Wayne

[Pete_UK]

Hello Wayne,

looks familiar !!

What you need to do is change "allocated" in the formula to a term which will return the fields that you want, concatenated together. For example, if you want to return the Customer name and the Location, separated by a comma-space, then you could use this to replace "allocated" (including the quotes):

INDEX(Jobs!$A:$A,MATCH(D$4&"*",Jobs!$K:$K,0))&", "&INDEX(Jobs!$B:$B,MATCH(D$4&"*",Jobs!$K:$K,0))

so that the formula in D5 would now become:

=IF(ISNA(MATCH(D$4&"*",Jobs!$K:$K,0)),"",IF(AND($C5>=VALUE(MID(INDEX(Jobs!$K:$K,MATCH(D$4&"*",Jobs!$K:$K,0)),FIND("_",INDEX(Jobs!$K:$K,MATCH(D$4&"*",Jobs!$K:$K,0)))+1,5)&":00"),$C5<=VALUE(MID(INDEX(Jobs!$K:$K,MATCH(D$4&"*",Jobs!$K:$K,0)),FIND("|",INDEX(Jobs!$K:$K,MATCH(D$4&"*",Jobs!$K:$K,0)))+1,5)&":00")),INDEX(Jobs!$A:$A,MATCH(D$4&"*",Jobs!$K:$K,0))&", "&INDEX(Jobs!$B:$B,MATCH(D$4&"*",Jobs!$K:$K,0)),""))

This can be copied across and down your table.

Hopefully you can see how to amend that term to allow you to bring other fields of data across - you will need to widen the columns in your table.

Hope this helps.

Pete

[warlordlovejoy]

Thanks again Pete, this helps and makes sense.....

Im not sure if the merging part can be done or not so that I only have one "box" for each job rather than "10 lines" if that makes sense??!!

Thanks again

[Pete_UK]

A formula couldn't do the merging, and there is a formula in the next cell anyway which will be lost if you did try to merge the cells (manually or with VBA). It doesn't help, also, that you have added 5 minute increments to the file instead of 15, so now you have 3 times as many formulae - are your drivers (and traffic around your way) predictable to 5 minutes??

You could possibly achieve something like you want by applying conditional formatting, such that if a cell is not empty AND is the same as the previous one then use a background colour and foreground colour the same, so that they appear empty but are the same colour as the first cell in a block.

Hope this helps.

Pete

[Pete_UK]

Hi Wayne,

I carried on with this yesterday but then forgot to post it. I've set this up using the same colours as in your status column, and have brought the first 4 characters of status to control the conditional formatting - now you get blocks of colour and only the first row of data is visible (the other rows are there, they are just "hidden" by using the same foreground and background colours). I've changed some of the status values so that you can see 4 of them.

Hope this helps.

Pete