0

I have a workbook with two worksheets. On the first worksheet titled "Report", I want to get data from my second worksheet, titled "DataSets". Ideally, I would like to have three or four tables on DataSets, and in Report I would like to be able to get the values at the intersections.

I noticed when using tables, "Test1" does not display if I enter =Data1[]. I can't use defined names because the titles of the rows and columns are the same.

My "DataSets" worksheet looks like this:

enter image description here

Is there another way I should be attempting to do this that will allow similarly titled rows and columns but in different blocks of data?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Blankdud
  • 698
  • 5
  • 9
  • 22
  • @pnuts I never said that the data was the same. Aside from that, the data being split among different tables is simply a requirement. – Blankdud Nov 13 '15 at 01:46
  • It would be helpful to see what you want Report to look like. You should be able to do a two-dimensional lookup using Index/Match, but without seeing the hoped-for result I can only guess. – Doug Glancy Nov 13 '15 at 03:38

1 Answers1

0

you may try something like that:

=INDEX(([firsttabrange],[secondtabrange],...),MATCH(A3,[rowheader],0),MATCH(A2,[columnheader],0),MATCH(A1,{"Data1","Data2",...},0))

A1 = TableName (upper left Cell)
A2 = ColumnName (asuming all tables have the same names, just pick one)
A3 = RowName (like ColumnName)

Note: either do it for the whole table (always start at the TableName-Cell for all ranges or never do it)

if you need it to compare values, do it 1 time for each table but leave the tablename out, like:

 =INDEX([firsttabrange],MATCH(A3,[rowheader],0),MATCH(A2,[columnheader],0))

Do the same for each other table

Hint2: set a proper data validation for A1, A2 and A3 helps a lot at picking the data you want

however... i'm not sure if that is what you are looking for

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31