Populate columns in one sheet based off of a few lists in a different sheet

Hi,

This is my first post. However, I'm a longtime viewer.

I've spent quite some time trying to figure out the best way to approach my Excel problem.

The objective:

I would like to take at least 2 lists in a "Setup" sheet (it could be more lists later) and have them 'auto populate' cells in other columns in another sheet, "Results"(same workbook).

Example:

One list would have a number of questions (like a survey - for example, they are formated Q1_1_1, Q1_2_1, etc) and the other list would be of countries (for example, Brazil, India, China, Chile, etc). I need the list of questions to populate one column of the "Results" sheet. It would need to do that for every country listed in "Setup". I also need it to 'auto populate' the country in the column next to the questions in the "Results" sheet. It would be replicating the same list over and over for each country listed.

I want the users to be able to paste in their long list of questions in one column in "Setup" and their list of countries in another column, also in "Setup". That way the "Results" sheet columns will auto populate everytime with the appropriate questions and countries. Its important to note that the number of questions and the tags assigned to the questions can change. The list of countries will also be changing.

I know the quick and easy way is to simply copy and paste the list of questions and drag the countries down. But I'm convinced Excel can do this for me - I don't want people getting lazy or tired and messing things up.

If I have broken any forumn rules, please know that I did not intend to. Thank you for your help!!

Jim

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.

I eliminated the array functions on the Setup sheet to improve performance somewhat. The massive number of arrays functions still remaining on the Results sheet mean this workbook is still a bit of a dog. I believe it can be improved further if you explain exactly what one full row of data is actually collecting, those array+indirect functions might be rewritten to speed things up more.

Meanwhile, this accomplishes your original goal, too.

Your response #2 you missed my point. Don't tell me HOW you did it, tell me in a short sentence WHAT value you're trying to retrieve back to cell F2. What value retrieve back to G2?

So you're looking for the percentages of each LongLegendValue? F2 resolves to 5 so the result would be 8.3% of all answers?

The formula that would go in F2 would be:

=COUNTIFS(OFFSET(INDEX('Passport Survey - Annual Study'!$1:$1, MATCH($C2, 'Passport Survey - Annual Study'!$1:$1, 0)), 2, , 9998), COLUMN(A1),'Passport Survey - Annual Study'!$E$3:$E$10000, $D2)/$E2

Format that cell as percentage with a decimal or two, then copy that cell down and across your table. The workbook immediately loses its snailish behavior.

COUNTIFS() is a much simpler formula than my example shows.... learn that first.

=COUNTIFS(testrange1, criteria1, opttestrange2, optcriteria2, opttestrange3, optcriteria3, etc...)

My use of the OFFSET() method to find the date in row1, offset down 2 rows, then expand to 9998 rows, that makes it look overly complex.

Happy deciphering.