Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name used in a formula
Use
Edit = Replace What: WAPI With: XAPI Since you are linking to a closed workbook there is no formula that will do what you want. Indirect would be the correct function, but doesn't work with closed workbooks. The alternative to replace would be a macro. Regards, Tom Ogilvy Cynthia Gregory wrote in message ... I have a workbook that is updated with new sheet names about every six weeks. The worksheets all contain formulas that reference the different worksheets and when I change the sheet names, I have to go through the worksheets and change my formulas to reference the new names. This is a large workbook with over three hundred formulas within the different workbooks. What I would like to do is write the formulas to look at a cell on sheet (let's call it sheetNames) pull the sheet name from that cell and then use that name in it's formula. ex (='H:\BIT Corporate Load\BIT Metrics CL03Fall\CL03Fall Cust Sys & Mvmt Plogs\CSCS Cycle Data\Templates\[rsc-cycle1.xls]WAPI'!$E$6) I want to replace WAPI with the sheet 'sheetName'!A1 data. This would allow for me to change the sheet names in one place and all of my formulas would reflect the change. I hope this makes sense. Thanks for your help. -- Thanks, Cynthia G. "Information is just signs and numbers, while knowledge involves their meaning. What we want is knowledge, but what we get is information." --Heinz R. Pagels |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet name used in a formula
Thanks for your help. I will continue as I was. You guys are great!!
-- Thanks, Cynthia G. "Information is just signs and numbers, while knowledge involves their meaning. What we want is knowledge, but what we get is information." --Heinz R. Pagels "Tom Ogilvy" wrote in message ... Use Edit = Replace What: WAPI With: XAPI Since you are linking to a closed workbook there is no formula that will do what you want. Indirect would be the correct function, but doesn't work with closed workbooks. The alternative to replace would be a macro. Regards, Tom Ogilvy Cynthia Gregory wrote in message ... I have a workbook that is updated with new sheet names about every six weeks. The worksheets all contain formulas that reference the different worksheets and when I change the sheet names, I have to go through the worksheets and change my formulas to reference the new names. This is a large workbook with over three hundred formulas within the different workbooks. What I would like to do is write the formulas to look at a cell on sheet (let's call it sheetNames) pull the sheet name from that cell and then use that name in it's formula. ex (='H:\BIT Corporate Load\BIT Metrics CL03Fall\CL03Fall Cust Sys & Mvmt Plogs\CSCS Cycle Data\Templates\[rsc-cycle1.xls]WAPI'!$E$6) I want to replace WAPI with the sheet 'sheetName'!A1 data. This would allow for me to change the sheet names in one place and all of my formulas would reflect the change. I hope this makes sense. Thanks for your help. -- Thanks, Cynthia G. "Information is just signs and numbers, while knowledge involves their meaning. What we want is knowledge, but what we get is information." --Heinz R. Pagels |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet formula incorrect after copying to another worksheet | Excel Worksheet Functions | |||
using worksheet name in a formula but not current worksheet | Excel Worksheet Functions | |||
Formula for advance worksheet name to next worksheet name | Excel Worksheet Functions | |||
Copying a formula from one worksheet to another worksheet | Excel Worksheet Functions | |||
How do I take a cell in one worksheet and add it into the formula in a different worksheet in the same workbook? | Excel Worksheet Functions |