Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Worksheet formula incorrect after copying to another worksheet Karen Excel Worksheet Functions 2 November 12th 09 02:06 AM
using worksheet name in a formula but not current worksheet Sing Chung Excel Worksheet Functions 3 July 23rd 09 04:29 PM
Formula for advance worksheet name to next worksheet name David Frank Excel Worksheet Functions 2 February 9th 08 08:51 PM
Copying a formula from one worksheet to another worksheet Kathie Excel Worksheet Functions 1 May 24th 07 01:31 PM
How do I take a cell in one worksheet and add it into the formula in a different worksheet in the same workbook? Lexi Excel Worksheet Functions 3 September 20th 06 05:52 PM


All times are GMT +1. The time now is 02:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"