Define a sheet in an array using a cell value
 

Hello,

I have a problem trying to make a formula that can search for a value in an
array based on 3 starting values contained in 3 separate cells.

Each of the 3 starting cells contains :
1. sheet name
2. value to search in a row
3. value to search in a column.

Setting the sheet is the hardest part.

the main function is not a problem, an index with 2 match functions for
row_num and col_num but defining the arrays in which the functions search
seems difficult as the sheet name in the array has to be taken from a cell.

I also tried an Indirect (match(Adress)) solution but that doesn't worork
either, here is the formula I came up with:

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)

If you can tell me what to change for any of the 2 solutions or have a third
one please help. Important to mention is that I used to use a macro for this
operation but I can't do that any more, I have to use a function.

Thank you,

Define a sheet in an array using a cell value
 

Did you not see the reply I posted to your thread of yesterday? If
not, here's a link to it:

http://groups.google.com/group/micro...2daf2a9b7aa7a7

Please don't repeat a post when it's not necessary.

Pete

On Nov 14, 2:15 pm, VDU wrote:
Hello,

I have a problem trying to make a formula that can search for a value in an
array based on 3 starting values contained in 3 separate cells.

Each of the 3 starting cells contains :
1. sheet name
2. value to search in a row
3. value to search in a column.

Setting the sheet is the hardest part.

the main function is not a problem, an index with 2 match functions for
row_num and col_num but defining the arrays in which the functions search
seems difficult as the sheet name in the array has to be taken from a cell.

I also tried an Indirect (match(Adress)) solution but that doesn't worork
either, here is the formula I came up with:

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)

If you can tell me what to change for any of the 2 solutions or have a third
one please help. Important to mention is that I used to use a macro for this
operation but I can't do that any more, I have to use a function.

Thank you,

Define a sheet in an array using a cell value
 

Hello,

My hotmail account was reseted and I didn't see the notification for the
first topic, I didn't see it in my account info as an open thread so I was
under the impression that I wrote the message but didn't post it. Thank you
for the answer and the link to it, sorry for reposting.

Regards,
VDU

"Pete_UK" wrote:

Did you not see the reply I posted to your thread of yesterday? If
not, here's a link to it:

http://groups.google.com/group/micro...2daf2a9b7aa7a7

Please don't repeat a post when it's not necessary.

Pete

On Nov 14, 2:15 pm, VDU wrote:
Hello,

I have a problem trying to make a formula that can search for a value in an
array based on 3 starting values contained in 3 separate cells.

Each of the 3 starting cells contains :
1. sheet name
2. value to search in a row
3. value to search in a column.

Setting the sheet is the hardest part.

the main function is not a problem, an index with 2 match functions for
row_num and col_num but defining the arrays in which the functions search
seems difficult as the sheet name in the array has to be taken from a cell.

I also tried an Indirect (match(Adress)) solution but that doesn't worork
either, here is the formula I came up with:

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)

If you can tell me what to change for any of the 2 solutions or have a third
one please help. Important to mention is that I used to use a macro for this
operation but I can't do that any more, I have to use a function.

Thank you,