Referencing a Worksheet Name in Formula

[cardan]

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.

 

[cardan]

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

 

[fisch4bill]

ActiveWorkbook.Name will return the name of the file that currently has the
focus.

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
.