Hi,

Apologies, I couldn't find an exact home for this post so have put it in general.

I may be approaching this in the wrong way but I am doing dynamic charting using lookups as determined by a choice in a cell with data validation called 'Selection', e.g. =VLOOKUP(SELECTION,DATA,2) where I can change the column based on the cell I am populating, all fairly straightforward...

The problem comes in that one set of data is a bit more complicated that the rest e.g.

Food Type Plan Actual
Cheese Cheddar 12 4
Cheese Stilton 5 3
Cheese Wensleydale 4 4
Bread White 10 5
Bread Wholemeal 2 1

You get the gist.

The data validated list contains each Food e.g. Cheese, Bread etc. I have also made each change in food its own named range e.g. A2:D4 'Cheese' and A5:D6 'Bread' as for some reason I thought this would help. You may also notice that 'Cheese' has three variants and 'Bread' only has two. What I want to do is using my pick list, populate the cells that my chart is looking at with the contents of the named range. Am I over complicating this or am I just missing that magic formula that will populate the cells? I'd rather not have multiple charts if this can be avoided.

Thanks,

Mark