V
VDU
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,
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,