I've got stuck while working on an excel-based database, and I need some help figuring out the next step. Ideally I'd talk this through with someone over a coffee to find some inspiration, but lacking people with sufficient excel knowledge to ask, I turn to the good people this board. I hope you might be able to help, please?

To try and explain the situation: I have a workbook with a series of sheets (approx 100), each one named after a Region, and on each sheet I have data arranged under the following headings starting in column A:

Date, Company, Item1, Volume1, Price2, Item2, Volume1, Price2, Item3, Volume3, Price3, etc. to allow for a total of 25 items; worth noting that not every region uses 25 items, but some do and others might in the future, therefore each sheet allows for this possibility. I have allowed for up to 200 entries in total per Region

Each time that data is entered into these sheets (through a user form), it goes into the next available empty row, and starts with Date and Company, and depending on what ItemNumber the information relates to, and then goes into the corresponding columns. There will always be data in columns A and B of each row, but then C, D, E may be blank in one row while F, G,H will have data, etc.; there is no regular pattern to which group of columns contains data.

In a separate sheet, I am trying to make a report where I can view all Date, Company, Volume, Price information gathered for the Region and Item that I select from two dropdowns - so RegionA, Item2, or RegionEF, Item2, and so on. At the moment I am happy only looking at one Region and Item at a time.

I have got as far as displaying the data quite happily and neatly in my "report" table using an Index-Match system, but I started by placing the formula, with a different row component, into each row of the report - so row 1 of the report shows the data in row 1 of the Region table, and so on. Where the problem arises is if there is data in rows 3, 4, 5, but not in 6, 7, 8, and then again in 9, 10. Because the formula in each row is predicated on a specific row number, I get lots of blanks, and I would like to have the data all nice and compact, without blank rows.

Is there a way of doing this with formulas, so that I don't have to run VB? The easiest way, with VB would probably be to get all the information across from the relevant sheet into the report, then run a filter so you only see the information for the corresponding Region and Item. Not very neat, but before I go down that route, I'd like to try using just formulas. Any idea on how to make the formula skip the blanks?

Thanks,

Richard