Define a sheet in an array using a cell value


G

Guest

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

Advertisements

Ad

Advertisements

G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top