I have a number of workbooks which are identical in form. Each uses
multiple sheets. If a make a formula change in one and then copy the
formula to a different workbook the new formula references the original
workbook and sheet. How can I turn off this "feature" so that the copied
formula will not make any reference to the workbook, only the sheet?
Thanks



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Hi Ken,
To enter the same formula in the same cell in a number of worksheets:
1)Click on the sheet tab of the first worksheet, hold Shift and click on the
tab of the last worksheet. This is called 'grouping'
2)Type the formulas needed in each cell
3) Ungroup the sheets by right clicking the tab of any sheet and selecting
ungroup

Bernard




"Ken Runge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a number of workbooks which are identical in form. Each uses
> multiple sheets. If a make a formula change in one and then copy the
> formula to a different workbook the new formula references the original
> workbook and sheet. How can I turn off this "feature" so that the copied
> formula will not make any reference to the workbook, only the sheet?
> Thanks
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

When I need to do this, I put a " ' " (single quote) in front of the formula
so Excel treats it as text, then I copy the text and paste it. Then I remove
the single quote
Mark

"Ken Runge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have a number of workbooks which are identical in form. Each uses
> multiple sheets. If a make a formula change in one and then copy the
> formula to a different workbook the new formula references the original
> workbook and sheet. How can I turn off this "feature" so that the copied
> formula will not make any reference to the workbook, only the sheet?
> Thanks
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Thanks, while waiting for a reply I came up with the same answer as the only
possible work around. (I think I should just go back to using QPro, a much
better program!)

Mark Durrenberger wrote:

> When I need to do this, I put a " ' " (single quote) in front of the formula
> so Excel treats it as text, then I copy the text and paste it. Then I remove
> the single quote
> Mark
>
> "Ken Runge" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I have a number of workbooks which are identical in form. Each uses
> > multiple sheets. If a make a formula change in one and then copy the
> > formula to a different workbook the new formula references the original
> > workbook and sheet. How can I turn off this "feature" so that the copied
> > formula will not make any reference to the workbook, only the sheet?
> > Thanks
> >
> >
> >
> > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

WorkBOOKS not workSHEETS, but thanks anyway.

Bernard V Liengme wrote:

> Hi Ken,
> To enter the same formula in the same cell in a number of worksheets:
> 1)Click on the sheet tab of the first worksheet, hold Shift and click on the
> tab of the last worksheet. This is called 'grouping'
> 2)Type the formulas needed in each cell
> 3) Ungroup the sheets by right clicking the tab of any sheet and selecting
> ungroup
>
> Bernard
>
> "Ken Runge" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I have a number of workbooks which are identical in form. Each uses
> > multiple sheets. If a make a formula change in one and then copy the
> > formula to a different workbook the new formula references the original
> > workbook and sheet. How can I turn off this "feature" so that the copied
> > formula will not make any reference to the workbook, only the sheet?
> > Thanks
> >
> >
> >
> > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

as long as the sheet names and ranges are the same in each workbook, you can
use:

EDIT -> LINKS and then press the change source button and choose the current
workbook. I use this often and it beats making each formula text as it
performs the change to every cell with that link referenced.

HTH

ryanb..

"Ken Runge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> WorkBOOKS not workSHEETS, but thanks anyway.
>
> Bernard V Liengme wrote:
>
> > Hi Ken,
> > To enter the same formula in the same cell in a number of worksheets:
> > 1)Click on the sheet tab of the first worksheet, hold Shift and click on
the
> > tab of the last worksheet. This is called 'grouping'
> > 2)Type the formulas needed in each cell
> > 3) Ungroup the sheets by right clicking the tab of any sheet and
selecting
> > ungroup
> >
> > Bernard
> >
> > "Ken Runge" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > I have a number of workbooks which are identical in form. Each uses
> > > multiple sheets. If a make a formula change in one and then copy the
> > > formula to a different workbook the new formula references the
original
> > > workbook and sheet. How can I turn off this "feature" so that the
copied
> > > formula will not make any reference to the workbook, only the sheet?
> > > Thanks
> > >
> > >
> > >
> > > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

You may want to avoid the single quote. It's impossible to do an Edit|replace
and get that single quote back out.

I just change all equal signs (=) to dollarsigns followed by an equal sign
($$$$=).

Then I can edit|replace to insert them and edit|replace to remove them.



Ken Runge wrote:
>
> Thanks, while waiting for a reply I came up with the same answer as the only
> possible work around. (I think I should just go back to using QPro, a much
> better program!)
>
> Mark Durrenberger wrote:
>
> > When I need to do this, I put a " ' " (single quote) in front of the formula
> > so Excel treats it as text, then I copy the text and paste it. Then I remove
> > the single quote
> > Mark
> >
> > "Ken Runge" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > I have a number of workbooks which are identical in form. Each uses
> > > multiple sheets. If a make a formula change in one and then copy the
> > > formula to a different workbook the new formula references the original
> > > workbook and sheet. How can I turn off this "feature" so that the copied
> > > formula will not make any reference to the workbook, only the sheet?
> > > Thanks
> > >
> > >
> > >
> > > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

--

Dave Peterson
(E-Mail Removed)

That and word perfect...

Mark

"Ken Runge" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks, while waiting for a reply I came up with the same answer as the
only
> possible work around. (I think I should just go back to using QPro, a much
> better program!)
>
> Mark Durrenberger wrote:
>
> > When I need to do this, I put a " ' " (single quote) in front of the
formula
> > so Excel treats it as text, then I copy the text and paste it. Then I
remove
> > the single quote
> > Mark
> >
> > "Ken Runge" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > I have a number of workbooks which are identical in form. Each uses
> > > multiple sheets. If a make a formula change in one and then copy the
> > > formula to a different workbook the new formula references the
original
> > > workbook and sheet. How can I turn off this "feature" so that the
copied
> > > formula will not make any reference to the workbook, only the sheet?
> > > Thanks
> > >
> > >
> > >
> > > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > > -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----