Excel Help
Author
Discussion

loosemarbles

Original Poster:

1,863 posts

230 months

Monday 24th September 2018
quotequote all
I consider myself above average with Excel, but I can't seem to crack this 'issue.'

We make frequent collections from a large warehouse and they have a complex booking in process. They require a haulier to find the date they are collecting, move along a row and establish which crew is on night shift or day shift, and then find the corresponding contact details for the relevant shift.

I would like it so that a haulier can put a date in, and it spits out the relevant information. I thought originally a vlookup table could work, but it is looking for a date in a table, then needs to report what column number either a 'D' or 'N' falls in.

Any help much appreciated!


nickd01

639 posts

246 months

Monday 24th September 2018
quotequote all
A horrid combination of MATCH and INDIRECT functions could work:

E.g -- Look up the Date and then in that row find a "D". From there you know the column and so can assume Crew 1 is in Column 1

Then repeat the formula for "N" to look up the night crew

="Crew " & MATCH("D",INDIRECT("B" & MATCH(H4,B3:B10,0)+2 & ":F"& MATCH(H4,B3:B10,0)+2),0)

If I get more time, I'll try and see if it works.

loosemarbles

Original Poster:

1,863 posts

230 months

Monday 24th September 2018
quotequote all
nickd01 said:
A horrid combination of MATCH and INDIRECT functions could work:

E.g -- Look up the Date and then in that row find a "D". From there you know the column and so can assume Crew 1 is in Column 1

Then repeat the formula for "N" to look up the night crew

="Crew " & MATCH("D",INDIRECT("B" & MATCH(H4,B3:B10,0)+2 & ":F"& MATCH(H4,B3:B10,0)+2),0)

If I get more time, I'll try and see if it works.
Cheers. I think I have sussed it. It isn't clean, involves IFS functions and a lot of conditional formatting, but it now shows the relevant Crews for Day / Night shift depending on a set date.

Now to have it tested in anger!