On Jan 7, 11:06*am, "Mike" wrote:
"cardan" wrote in message

...



Hello,

I have an issue I can't seem to find an answer to, which I believe is
doable.

I have a worksheet named RU (for Rollup) which pulls information from
3 other worksheets named "RCA","RCB", "RCC" through an Index Match
formula.

I am hoping to insert into the formula the name of the tab I would
like the formula to search from. *I would like formula to look for the
name of the worksheet in a cell within the same row, find the
worksheet and then apply the index match formula. *That way I can just
the name of worksheet it looks from by changing the value of the cell.

I have tried researching the INDIRECT function but I can't seem to
find an example for what I am trying to do. *Any help would be greatly
appreciated. *Thank you for your time in advance.

Indirect should work. *Maybe you have incorrect syntax. The form is
=INDIRECT("'[filename.xls]SheetName'!$A$1") *Using that syntax you can
further formulize (is that a word??) it like this

Cell a1 = "filename.xls"
Cell a2 = "SheetName"
Cell a3 = "a"
Cell a4 = 1

=indirect(" '[ "&a1&"]"&a2&" '! "&a3&a4) * note the ' are needed and space
was added for readibility.

Note that the file must be open for this formula to work. *You should be
able to take it from there.

Mike

Thank you for the response Mike. Question, I am trying to reference
tabs within the same workbook. Do I still need reference the workbook
name? If so, is there a formula that will pull the file name? (I am
anticipating the file name will change with versions and work being
done) Thanks again!

Dan