| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Worksheet Name in Formula
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing a Worksheet Name in Formula
"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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing a Worksheet Name in Formula
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing a Worksheet Name in Formula
ActiveWorkbook.Name will return the name of the file that currently has the
focus. "cardan" wrote: 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing a Worksheet Name in Formula
"cardan" wrote in message ... 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 You don't need the workbook name if the tabs are inside of the same workbook as the indirect formula |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace worksheet without affecting the referencing formula | Excel Discussion (Misc queries) | |||
| referencing another worksheet in a formula | Excel Programming | |||
| VB formula referencing other worksheet | Excel Programming | |||
| Formula Referencing Worksheet Name by Variable | Excel Programming | |||
| Formula Referencing Worksheet Name by Variable | Excel Programming | |||
Linear Mode
