Referencing a Worksheet Name in Formula

C

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.
 
M

Mike

cardan said:
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
 
C

cardan

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
 
F

fisch4bill

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

cardan said:
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
.
 
M

Mike

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

You don't need the workbook name if the tabs are inside of the same workbook
as the indirect formula
 

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