How can I use a sheet in a formula based on the value in a cell

[Guest]

I have a problem trying to make a formula that searches for a value in a
certain table but it chooses in what table based on the value in another
cell. Each table is in a separate sheet.

Basically I need a function to get the value in cell B2, search in the sheet
named exactly like the value in B2. The search is made in a matrix but is not
the problem, an easy index function with the row and column determined using
2 match functions. It's getting the functions to search in the right sheet,
making the arrays in the index function and the 2 match functions to depend
on the value in cell B2 that I found problematic.

Using a different aproach, I cam up with this:

INDIRECT("'"&B$2&"'!R"&(10+Match(B5, address(11, 2, 1, true,
B2):Address(65000, 2, 1, true), 0))&"C"&(2+Match(A5, address(2, 3, 1, true,
B2):Address(2, 200, 1, true, B2), 0)), false), FALSE)

But again it doesn't work, probably the address funtion is not used correctly.

If you can please help me with any of the 2 approaches or have a 3rd one
that works please do. One thing to mention, until now I used a macro to do
the same thing, I can't use it any more, it has to be with functions.

Thank you,

 

[Pete_UK]

It would help if there was a bit more detail. Are your tables in
exactly the same cells in all the sheets? I suggest you get the
formula working for just one sheet, so that it is something like:

=INDEX(Sheet1!table,MATCH(cell1,Sheet1!range1,0),MATCH(cell2,Sheet1!
range2,0))

and then wherever you have Sheet1! you can replace this with:

INDIRECT("'"&B$2&"'!range_n")

Hope this helps.

Pete

 

[Herbert Seidenberg]

Pete_UK has the perfect solution.
Here is his formula with a few more details:
=INDEX(array1,
10+MATCH(RN,INDEX(INDIRECT("'"&Tab&"'!Array3"),2,),0),
2+MATCH(CN,INDEX(INDIRECT("'"&Tab&"'!Array3"),,2),0))
Array1 is some table on Sheet1
Tab contains the text SH3
Array3 is some table on sheet SH3
RN contains the number you are searching for in row 2 of Array3
CN contains the number you are searching for in column 2 of Array3
10 is added to the row location of RN and becomes the row location for
Array1
2 is added to the column location of CN and becomes the column
location for Array1