Complicated lookup - Index Match

Please Login or Register  to view this content.

Perhaps encapsulate with IFERROR for rows where there are no "Y".

Hi,

In EZ6 copied across and down

Formula:

Please Login or Register  to view this content.

Yes that is complicating things.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains the following columns


Company
Area / Post code- (only one area on each row)
Area - a vlookup formula
Region - a vlookup formula
Home - containing values like Home, Home 1 bed, 2 bed..etc all the column labels on row 1 of Data Input
Number value - i.e. the numbers in J2:BC149 on the Data Input sheet


Once you have this normalised 2 dimensions database analysing it will be done with a Pivot Table and you'll avoid very complex formulae

You will of course have no more entries than you have now, but the table will be many rows deep.