1

I spent hours searching for a solution to adapt and then trying a million modifications to what I found and have finally admitted defeat.

I work with survey data where a workbook has one worksheet for each question in a survey. Across the columns are different groups (i.e. male, female, etc.) and going down the columns are the answer options for the questions. The cells below each column represent the percentage of that group fitting the answer option (i.e. in the female column, 13% are 18-24, 17% are 25-34, etc.). A worksheet might have 50 columns of groups.

I have created a separate worksheet where type the name of a worksheet (i.e. the question name: "AGE") in cell $O$2, answer options in O4 (and down that column) and the name of group across rows starting in P3, (i.e. "Females" in P3, "Males" in P4, etc.).

I have a formula working to populate the answer options, but would like a formula in P4 that looks up the column in the QID - TITLE worksheet with the same value found in P3 and returns the value that is in that column and the row that aligns with the label in column O (i.e. "answer #").

I have been trying to make this vlookup formula work

=VLOOKUP(O5,'QID - TITLE'!$E$2:$TY$45,4,FALSE)

if I knew how to make the following changes:

  • Change the column index number (4) to point to the column that has the same name as P3. This will make the group dynamic...I just need to type in different group names across row 3 starting at column P and the table and graph to the left will populate with data on the new group.

  • Change the table array to point at the worksheet with the name in O2. This will make the formula dynamic so I just need to change O2 and the formulas will point to a different worksheet (data for a different question).

Hopefully I've provided a clear description. Happy to provide any clarity and thanks in advance for any replies.

HERE IS THE WORKSHEET THE FORMULA NEEDS TO GO IN:

enter image description here

HERE IS THE "QID - TITLE" WORKSHEET THE FORMULA NEEDS TO POINT TO.

enter image description here

Morphasis
  • 31
  • 6
  • 1
    Could you provide a screen shot of the QID - TITLE sheet as well plz? As @pnuts says, it should be do-able. – Tom Sharpe May 01 '17 at 07:43
  • Thanks for taking a look at the question. I've added the image of the QID - TITLE worksheet to original question. – Morphasis May 01 '17 at 13:34
  • pnuts - Thanks for the reply. I'll play around the the MATCH and INDIRECT functions and see what I can accomplish. – Morphasis May 01 '17 at 13:38

1 Answers1

1

Try this Formula: 

=IFERROR(INDEX(INDIRECT("'"&$O$2&"'!$E$2:$TY$45"),MATCH($O4,INDIRECT("'"&$O$2&"'!$E$2:$E$45"),0),MATCH(P$3,INDIRECT("'"&$O$2&"'!$E$2:$TY$2"),0)),"")

E2:TY45 all the Data in QID-TITLE sheet like in your formula, column E for answers $E$2:$TY$2 the header (All,...) in QID-TITLE
$O4 Answer1... $ to fix the column
P$3 the header $ to fix the row
and you can drag it down and in the row
IFERROR to return empty in case no answer

yass
  • 849
  • 1
  • 7
  • 13
  • yass...WOW! I literally just paste your formula into the cell and it worked. Thank you so much. I guess I should have asked soon. Now I'm going to study it so I understand how you did it. Thank you again. – Morphasis May 01 '17 at 21:02
  • If you need any more details just ask – yass May 02 '17 at 15:46
  • Thank you again for the replies. While Excel formulas aren't my strength, I'll hopefully find a way to pay it forward with someone else. – Morphasis May 05 '17 at 23:11