I have several tables in one tab which I have named individually. In another tab I have a table that I wish to populate with the same information of the selected table based on a drop down selection. All tables are the same size. The dropdown is also a named range.
我在一个标签中有几个表,我单独命名。在另一个选项卡中,我有一个表,我希望根据下拉选项填充所选表的相同信息。所有表都是相同的大小。下拉列表也是命名范围。
The list in the dropdown is the name of each table. The following is my approach but I'm off by a row. 'PRCoverage' is the name of the dropdown.
下拉列表中的列表是每个表的名称。以下是我的方法,但我连续离开了。 'PRCoverage'是下拉列表的名称。
=INDEX(INDIRECT(PRCoverage),ROW()-ROW($B$7),COLUMN()-COLUMN($B$7))
The issue is that the tables don't all start in the same row. I'm trying to figure out to way to reference the first row in each table.
问题是这些表并非都在同一行中。我试图找出引用每个表中第一行的方法。
2 个解决方案
#1
1
To begin with, I've put everything onto one sheet for screenshots, this will work across multiple pages.
首先,我将所有内容都放在一张工作表上以获取屏幕截图,这将适用于多个页面。
- I began by having a list I could use for a drop down list in cell
F1. This is the named range inD2:D3calledTableList. Once I had this name, that is how cellF1validates. - Next, you will see in the formula in
F1that I have two named ranges. These are dynamic named ranges. To create them: Select cellF1, then CTRL+F3 to open the name manager. Then select the "New" button. For this demonstration I named itMale_Characters. For the formula I used:=Sheet1!A3Please note I am not using absolute references (no "$"). Repeat this for the starting corner of every table you want to display. - Finally, create a nested if statement that works through each table as you can see in my screen shot.
=IF($F$1=$A$1,Male_Characters,IF($F$1=$A$12,Female_Characters,""))is the same code in all the cells of my display table.
我开始有一个列表,我可以用于单元格F1中的下拉列表。这是D2中的命名范围:D3称为TableList。一旦我有这个名字,这就是细胞F1验证的方式。
接下来,您将在F1的公式中看到我有两个命名范围。这些是动态命名范围。要创建它们:选择单元格F1,然后按CTRL + F3以打开名称管理器。然后选择“新建”按钮。在本次演示中,我将其命名为Male_Characters。对于我使用的公式:= Sheet1!A3请注意我没有使用绝对引用(没有“$”)。对要显示的每个表的起始角重复此操作。
最后,创建一个嵌套的if语句,可以在我的屏幕截图中看到每个表。 = IF($ F $ 1 = $ A $ 1,Male_Characters,IF($ F $ 1 = $ A $ 12,Female_Characters,“”))是我显示表的所有单元格中的相同代码。
Sample Spreadsheet: Click Here
示例电子表格:单击此处
#2
1
We can get the in which entries of particular start with something like the following:
我们可以通过以下内容获取特定条目的条目:
=ROW(INDIRECT(MyDropDown))
I then use OFFSET to fill my TABLE
然后我使用OFFSET来填充我的TABLE
=OFFSET(INDIRECT("Sheet2!A" & ROW(INDIRECT(MyDropDown))),ROW()-2,COLUMN()-1)
But you can just as easily swap OFFSET for INDEX
但您可以轻松地将OFFSET替换为INDEX
=INDEX(INDIRECT("Sheet2!A" & ROW(INDIRECT(MyDropDown))),ROW()-2,COLUMN()-1)
#1
1
To begin with, I've put everything onto one sheet for screenshots, this will work across multiple pages.
首先,我将所有内容都放在一张工作表上以获取屏幕截图,这将适用于多个页面。
- I began by having a list I could use for a drop down list in cell
F1. This is the named range inD2:D3calledTableList. Once I had this name, that is how cellF1validates. - Next, you will see in the formula in
F1that I have two named ranges. These are dynamic named ranges. To create them: Select cellF1, then CTRL+F3 to open the name manager. Then select the "New" button. For this demonstration I named itMale_Characters. For the formula I used:=Sheet1!A3Please note I am not using absolute references (no "$"). Repeat this for the starting corner of every table you want to display. - Finally, create a nested if statement that works through each table as you can see in my screen shot.
=IF($F$1=$A$1,Male_Characters,IF($F$1=$A$12,Female_Characters,""))is the same code in all the cells of my display table.
我开始有一个列表,我可以用于单元格F1中的下拉列表。这是D2中的命名范围:D3称为TableList。一旦我有这个名字,这就是细胞F1验证的方式。
接下来,您将在F1的公式中看到我有两个命名范围。这些是动态命名范围。要创建它们:选择单元格F1,然后按CTRL + F3以打开名称管理器。然后选择“新建”按钮。在本次演示中,我将其命名为Male_Characters。对于我使用的公式:= Sheet1!A3请注意我没有使用绝对引用(没有“$”)。对要显示的每个表的起始角重复此操作。
最后,创建一个嵌套的if语句,可以在我的屏幕截图中看到每个表。 = IF($ F $ 1 = $ A $ 1,Male_Characters,IF($ F $ 1 = $ A $ 12,Female_Characters,“”))是我显示表的所有单元格中的相同代码。
Sample Spreadsheet: Click Here
示例电子表格:单击此处
#2
1
We can get the in which entries of particular start with something like the following:
我们可以通过以下内容获取特定条目的条目:
=ROW(INDIRECT(MyDropDown))
I then use OFFSET to fill my TABLE
然后我使用OFFSET来填充我的TABLE
=OFFSET(INDIRECT("Sheet2!A" & ROW(INDIRECT(MyDropDown))),ROW()-2,COLUMN()-1)
But you can just as easily swap OFFSET for INDEX
但您可以轻松地将OFFSET替换为INDEX
=INDEX(INDIRECT("Sheet2!A" & ROW(INDIRECT(MyDropDown))),ROW()-2,COLUMN()-1)