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,
=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