VLOOKUP with multiple criteria for reporting

[McFab]

I would like to have the ability to create a report using a VLOOKUP statement that is between two dates as well as a second value. I have attached a spreadsheet with the details if anybody can help. “Report 1” is used for simple reporting but I would like to automate a second report using specific criteria, if possible.

[Glenn Kennedy]

This looks a bit of a mess (sorry!!). Merged cells are ABSOLUTELY going to make this more difficult than is needed. However, you are making it EVEN WORSE by using 12 merged rows per entry, when you only "need" 3.

1. Can you get rid of the merged cells COMPLETELY?

2 If not, can you redesign your sheet so that you use ONLY three merged rows per entry and re-post it?

[AliGW]

I looked at this, but the layout put me off of offering help. I'm glad I wasn't alone. I agree with Glenn - the whole thing needs a rethink. Sorry!

[McFab]

Thanks Glenn, I built the first report some years ago and it's not clear to me at the moment why I did all that. I have re designed the report so I hope it helps.

[Glenn Kennedy]

All formulae are array formulae.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

The formulae are still rather complicated, as they need to return blanks here and there to preserve the appearance of things. However, they can be copied down, simply by dragging, as far as you need. There are three variants of the same formula (identified by different coloured shading). Here's the simplest of them (pale yellow):

Please Login or Register  to view this content.

Happy to try (!!!) to explain them, if needed!!!

[McFab]

Thanks again Glenn, Thank you very much for your work, I have looked at your results and they are way above my capability. I do have a couple of questions if you don’t mind.
1. I couldn’t get the report to flow into the second page. When completed, I will have up to 10 pages in the report so I need to know how to complete that.
2. When I move the report across to the live spreadsheet, I will have around 10,0000 lines in the “Master Report” which will grow ever day. Do I just adjust the numbers in the array for the “Master Sheet” (Master Sheet'!$B$1:$B$24 to Master Sheet'!$B$1:$B$12000)

Now I’ve had a look at the completed report, I think I over complicated things again. Now looking at it with fresh eyes, I just really needed the “Ticket” information the other information I can complete using a basic VLOOKUP.

[Glenn Kennedy]

Whatever...

1. Don't put page breaks in yourself. Let Excel do it. Page Layout/Print Titiles and select Row8 to be repeated on every page. Then go to Page break preview. (View/PageBreak preview). Drag the automatically-entered page breaks (dotted blue) up/down to give you the number of lines per page you need.

2. Yes.

[McFab]

Thank you very much Glenn, all sorted. I transferred the Array Formulae across the live report and tested it for a couple of days, all is going well.
Thank you again for all your help, it was much appreciated.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.