Collating data from another tab based on a pop up menu

Do all of the tables have the same number of columns with the same headings/data in the same columns? The formula assumes all of the tables are set up exactly the same way. Make sure you don't have any hidden columns or extra columns in any of them. Looking at the results, it appears that half of the table have an extra column to the right of column A. It appears most of these extra columns are empty but some have numbers in them. The column for "vnum" must be column B, "str" must be column C, etc. While there is the possibility the app has a bug causing this problem lets hope the issue is the tables not being the same.

Did you fill down to the end of the table and only the first row worked?

The IFERROR masks all errors (other than syntax errors) making it hard to tell what it is wrong. You can remove that part of the formula and see what the error is. The two obvious choices are a reference error or a match was not found.

Remove the IFERROR( at the beginning and ,"") at the end. Try that out first in the entire table. You'll have errors in any row with no option chosen but any rows that have an option chosen in column B should give results.

• Is the table on the Finger 1 sheet named Table 1?
• Is that table set up like the others: one header row, one header column and goes out to column R?
• Are there any extraneous spaces in the text "Finger 1", such as a trailing space? The name in the table and the name of the actual sheet must match exactly.
• Is there an entry in that table for "Troll ring"?
• Does "troll ring" in the table or in your popup have any extraneous spaces?

One way would be to use INDIRECT to create a reference to the correct sheet and table then use MATCH to get the correct row and OFFSET to get the data from each cell of that row.

I assume the sheets are named exactly the same as the categories and that all the category tables are Table 1.

Insert two new columns, C and D, into your table

Formula in C2 =A2&"::Table 1"

Formula in D2 =MATCH(B2,INDIRECT(C2&"::A"))

Fill down to complete the columns

Column C creates the string used for making a reference to the table. The result is Sheet_name::Table 1 .

Column D uses INDIRECT to reference column A of that table and uses MATCH to find the row that has the chosen option.

Column E (the 5th column) will be the first column to get data.

E2 =IFERROR(OFFSET(INDIRECT($C3&"::A1"),$D3−1,COLUMN()−4),"")

Fill across to complete the row

Fill down to complete the rest of the rows and columns.

OFFSET(base cell, row offset, column offset)

The base cell is referenced using INDIRECT again. The base is cell A1 of Table 1 of the chosen sheet.

The row offset is the row we determined before, minus 1.

The column offset if the current column minus 4. Column E is 5 so, for column E, the offset will be a 1.

IFERROR is necessary for when you have chosen "none" in your popup.

Hide C&D when it is all set up.

I often keep thinking of the problem after I have posted, especially when it is a complicate set of formulas and I think I can do better. Here is another option.

No new columns are required. The first column to receive data is column C, same as shown in your screenshot.

I used A:G in the formula below because that is all the columns I had in my tables. Change it to correspond with the columns in your "category" tables. Your screenshot showed up to column S but they apparently go farther.

Formula in C2 =IFERROR(VLOOKUP($B2,INDIRECT($A2&"::Table 1::A:G"),COLUMN()−1),"")

A reference error means one of the cell references in the formula does not exist

B4 definitely exists

A4 definitely exists

The one we are creating that is A4&"::Table 1::A:R" is the obvious problem.

An example would be Finger 1::Table 1::A:R

The Finger 1 sheet exists

Is the table on that sheet named Table 1? That was not shown in your screenshot.

Does it have columns out to R? It appears to in your screenshot.

I suspect it is the table name. It appears the table on sheet "Light" is named Table 1 and the others are not. If you have named the tables the same as the sheets, either change them all back to Table 1 or change them all to be named the same as their sheet. You can use this reference instead (starting in row 3) if they are named exactly the same as the sheets:

A3&"::"&A3&"::A:R"