Complex: Analysing data with uncertain column locations

Hi everyone!

So it's my first week using excel and I've taken on work way above my knowledge level, hoping someone might be able to help me out. Basically I am creating an excel file which analyses data and produces stacked bar charts from this. Unfortunately I cannot manipulate the way the data is organised/labeled in excel.

The data is inputed into sheet 1 in the following format.


Item type........Do you like apples?.........Do you like pink?.........Do you like red?
Item....................Agree...............................Disagree...................Don't know
Item....................Disagree...........................Don't Know.................Agree
Item....................Don't Know........................Agree.......................Don't Know
Item....................Neutral..............................Agree.......................Neutral




From this I basically want to make a stacked bar chart of people who agree/disagree/don't know/neutral to each of the questions (Do you like apples, etc). Presently I have been using the following formula (this formula is for the "Do you like apples question"):

=COUNTIF(Sheet1!B1:B6,"agree")*100/(COUNTIF(Sheet1!A1:A999,"item"))

=COUNTIF(Sheet1!B1:B6,"Disagree")*100/(COUNTIF(Sheet1!A1:A999,"item"))

=COUNTIF(Sheet1!B1:B6,"Don't Know")*100/(COUNTIF(Sheet1!A1:A999,"item"))

=COUNTIF(Sheet1!B1:B6,"Neutral")*100/(COUNTIF(Sheet1!A1:A999,"item"))


from this I get a set of percentage values which I use to make the stacked bar chart. This is all well and good however I have just been informed that the ordering of the questions will change every now and again (so the apple question is no longer in B1, and might start in C1 or B3 or D2) and as this is an analysis they will run several times a year I need it to be smart enough to search for the question not the cell range. So now I am thinking I need some kind of formula to search for the question (Do you like apples?) within the spreadsheet rather than just specify where I imagine the question will be when the data is imported in.

Many of you are probably cringing at my rudimentary approach, so I apoligise if my formula is burning your eyes but I would be the most thankful person alive if somehow could help me out.

Thanks!