VLOOK UP and = to help

Mmmm. There are lots of pitfalls here. Firstly, you have a lot of merged cells (yellow shading on the two weekly sheets). Can you get rid of them? They will make maintenance of any formulae a TOTAL nightmare. They are best avoided entirely.

Secondly, do I understand you correctly that you want to choose the entries in the 3 tan coloured cells in sheet 1 from dropdowns and then get Excel to do its stuff? If so - what is the point of the DD in D2. Is the same information not being displayed in D5? If so - why have it displayed twice?

On the Lists sheet, I have created a dynamic list of sites (just add as many as you need) and sheet names (just add a new sheets by hitting the + button and this list will update - copy the formula down further if needed).

I have set up 2 dynamic dropdowns based on this list and have put in a formula to return the Engineer on call. I have taken out ALL the nasty merged cells on the two data sheets. However, before I go any further, I would like you to do two things.

1. Check it over and let me know what you think.

2. Rework a real example of the data on those two date sheets (just a couple of examples) so that I can test the next formulae further.

3. Repost your modified sheet.

One of the formulae (the one that lists the names of the worksheets) relies on an old internal Excel macro-function; so remember to enable macros when you open the sheet.

I wasn't asking for real real data - more of a reality check that the structure was OK. Imaginary people like Donald Duck would have been fine. Your sheet did however confirm that there can be up to 6 results for each area (Long Larton).

So I went ahead and took this a bit further. Does this look like what you need? Can you live with the structure that I have imposed on the weekly sheets?

Exactly which numbers do you want included? Where are they currently?

Is this the way you want it to look? If so, I can begin to explain what's been going on.

OK... Here goes.

Sheet Lists Column A. Put all your sites here. You can then sort them into alphabetical order. I have set up a Named Range in column A that will adjust automatically to include all the sites in the dropdown list in Summary D5. To see the formula that achieves that, hit CTRL and F3. It's called "Sites".

Sheet Lists Column B. Two things going on here. Another Named range (called "Sheets", again CTRL F3 to view) that uses an old Excel "macro" to assist in returning a list of worksheets in the file. Basically, if you create a new sheet, the list in Lists!B:B will automatically update to include its name. It returns the sheets in the order left to right on the Tab list, so I put the summary sheet at the left hand side. the formula in Lists!B:B is one I copied from another www and is an array formula (must be set with CTRL-SHIFT-ENTER, not ENTER to work - if all is well, Excel will enclose the formula inside a pair of {}. the formula (and the Named Range) are set to allow for 100 sheets in total. Hopefully that'll be sufficient!!

I then used Lists!B3:B100 to creat another named range ("Dates") which contains those sheets. i used that Named Range to form the Dropdown box list in Summary!C4. The "Sites" Named Range was used for the dropdown box in Summary!D5.


then, I used a fairly straightforward LOOKUP-type formula (INDEX-MATCH) which allows for the return of MULTIPLE results with ONE criterion. the criterion is the site name. However, I had to use an INDIRECT function to pick up the Sheet name that it needed to look at from Summary!C4. That makes the formulae that return the Engineer(s) on call and the multiple results for the escalation routes look a bit formidable. If you want, I'll try to explain them in a bit more detail later.

I also created a "blank" sheet for the date (Template), which you could use to create new sheets for new dates.

Have a play with it and try to break it and I'll help you fix it, if needed.