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.
我使用调查数据，工作簿中每个问题都有一个工作表。跨列是不同的组(如男性、女性等)，沿着列向下是问题的答案选项。每个列下面的单元格表示符合选项的组的百分比(例如在女性列中，13%是18-24,17%是25-34，等等)。一个工作表可能有50列组。
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.).
我创建了一个单独的工作表，其中在单元格$O$2中键入工作表的名称(即问题名称:“AGE”)，在O4中键入答案选项(在该列下面)，并在P3中开始的行中键入组的名称(即。P3中的“女性”，P4中的“男性”等)。
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 #").
工作的我有一个公式来填充答案选项,但在P4看起来想一个公式列QID -工作表标题相同的值在P3和返回值的列和行与列的标签O(即。“答案#”)。
I have been trying to make this vlookup formula work
我一直在尝试让vlookup公式工作
=VLOOKUP(O5,'QID - TITLE'!$E$2:$TY$45,4,FALSE)
= VLOOKUP(O5 QID -标题”! $ E $ 2:泰45美元,4,假)
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.
将列索引号(4)更改为指向与P3名称相同的列。这将使团队充满活力……我只需要在第3行输入不同的组名，从第P列开始，左边的表和图将填充新组上的数据。
-
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 changeO2and the formulas will point to a different worksheet (data for a different question).将表数组更改为指向工作表的名称(在O2中)。这将使公式具有动态性，所以我只需要改变O2，公式将指向不同的工作表(针对不同问题的数据)。
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:
这是公式需要填写的工作表:
HERE IS THE "QID - TITLE" WORKSHEET THE FORMULA NEEDS TO POINT TO.
这是公式需要指向的“QID - TITLE”工作表。
在这里输入图像描述
1 个解决方案
#1
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
E2:TY45 QID-TITLE表单中的所有数据，如公式，E列回答$E$2:$TY$2 header(全部，…)QID-TITLE $O4 Answer1…$用于修复列P$3 header $用于修复行，您可以将它拖下来，在行IFERROR中返回空，以防没有答案
#1
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
E2:TY45 QID-TITLE表单中的所有数据，如公式，E列回答$E$2:$TY$2 header(全部，…)QID-TITLE $O4 Answer1…$用于修复列P$3 header $用于修复行，您可以将它拖下来，在行IFERROR中返回空，以防没有答案