Converting form data to tabular data from multiple sheets and workbooks

[BlissC]

Hi, I have a problem in that I've 'inherited' data presented in Excel which three colleagues all want in different formats (despite repeating asking that if they want me to work on data for them and monitor the completion of work, to talk to me about what data they're going to want out of it and in what format before they start setting things up themselves!).

The data is for garden maintenance which is basically the repair work that needs doing. My colleague who's surveyed the gardens has set up a kind of form setup in Excel with an Excel workbook for each street, and within each workbook each sheet holds the records one property. The workbooks therefore have a varying number of sheets, named for example 5 High Street, 7 High Street, 9 High Street, etc., and there are a number of streets which have been surveyed, so there are a number of workbooks each with a number of sheets.

Colleague 2 is happy with the information in that format, but also wants a summary of the work required and the cost, so has added a table that summarises the work/cost. He has been manually adding up the quantities of repairs required and the costs. I've put formulas into the summary table to automatically calculate repairs, quantities and costs. My initial problem was that Colleague 1 was using the same basic layout, but occasionally adding in extra rows, so the data was all over the place. I've had to standardise the form to make sure the data's in the same place on each (unfortunately because work's already started, I'll have to copy and paste the already completed surveys into my standardised format) but I've asked my colleague to use the standardised version from now on.

Colleague 3 though wants the data in list format. My problem here is that I have multiple workbooks with multiple sheets that need to be in one single list (table). I'm thinking that I probably need to get the the data in each workbook into a single table for that workbook, and then get the table of data for each workbook into a new single table in a different workbook, but my problem is that I have no idea how to accomplish that. My first instinct was that I probably needed to somehow use vlookup to get the data from each sheet, but I've no idea how that works with multiple sheets/workbooks, and have the added complication that the layout of the data on each sheet isn't in a nice simple tabular form.

I'm guessing that I need to use a multi-step approach to get the data all into one list/table on a single sheet, but to be honest I've no idea where to start with this one. I'd be grateful if anyone could offer any advice about how I need to go about this. I've attached the standardised form so that you can see what the data is that I'm dealing with. In the attached example there's just one sheet for one property, but in a completed workbook there will be many tabs - one for each address.

Many thanks.

[alansidman]

Closed at OP request. Will repost in Commercial Services.