Need formula to find correct worksheet and column from text label

I understand the restrictions the layout imposes, and agree it could well be improved. But what I sent reflects what the End User has stipulated.

I did nor respond immediately to Jason or Pete because I was "AFD" for a while and have spent several hours trying to get the approaches to work on the "real" file, without success because of some curious anomolies.

The attached update 151031 Comparisons v2.xlsx which is the actual column structure of the real file, although only a small section of the rows, works perfectly for the "monthly" comparisons until you select April 2017 onwards, From April - Dec the formula pulls up the annual results for 2017.

Secondly, it generates numbers on the "Indirect" bloc from the same point that don't link to any of the sheets,

And thirdly, selecting any of the Quarterly or annual summaries generates an Error Message.

I'm sure it will be a simple "tweak" that I've missed, and will be grateful as ever if someone can point the error(s) out.

Ochimus

Originally Posted by Ochimus

But what I sent reflects what the End User has stipulated.

until you select April 2017 onwards, From April - Dec the formula pulls up the annual results for 2017.

Secondly, it generates numbers on the "Indirect" bloc from the same point that don't link to any of the sheets,

And thirdly, selecting any of the Quarterly or annual summaries generates an Error Message.

But remember, end users rarely understand the real world, while there are exceptions, most tend to fall between housefly and pigeon where common sense is involved.

Which formula was pulling annual results instead of monthly for 2017? I can't reproduce that problem.

Were the numbers from the 'Indirect bloc' somewhere in the range of 42000 to 43000 ? Those numbers would represent the dates used with your data, today = 42309 (number of days from 1 Jan 1900).

Both formula take the month and year from the criteria cell and convert it to a real date in order to identify the correct column. Qtr doesn't represent a real date, which is why it causes errors.

This formula works with your latest example, but I wouldn't want to use it in a real world scenario. Given the amount of processing power this would need with a few thousand rows of data, it's going to more hazardous to the environment that a VW diesel!