Need some help - PLEASE

[Flipkid2]

Sorry that I only post when I need help, but I am desperate.

I am using a spreadsheet for work. It has 19 tabs on it.

The 18th tab takes numbers from many multiple cells from the first 17 tabs. Because the cells are not together, when I cut and pasted the formulas, they were all out of whack and I had to correct a few hundred cells one at a time. Also the first 17 tabs are tabs that are copied from other sheets one at a time.

The 19th tab takes totals from the 18th tab

Anyway....I finally got the sheet the way I need it but here is my problem. The last 2 tabs are tabs that I need to continually use. The way I need to use it is to first compile the sheet with the first 17 tabs and then copy in the last 2 tabs. The problem is that every time I paste in the last 2 tabs, it changes the formulas to include the file name of the original sheet. I have tried a million different things and can not figure it out.

Hopefully I have explained this clearly enough so that someone can help me.

[Dave Peterson]

I change my formulas to strings, copy|paste, then change my strings back to
formulas.

In the sending workbook/worksheet
select the range to copy
edit|Replace
what: =
with: $$$$$=
replace all

Copy and paste

Select the pasted range
edit|Replace
what: $$$$$=
with: =
replace all

(don't forget to fix the original sheet, too.)

Flipkid2 wrote:
>
> Sorry that I only post when I need help, but I am desperate.
>
> I am using a spreadsheet for work. It has 19 tabs on it.
>
> The 18th tab takes numbers from many multiple cells from the first 17
> tabs. Because the cells are not together, when I cut and pasted the
> formulas, they were all out of whack and I had to correct a few hundred
> cells one at a time. Also the first 17 tabs are tabs that are copied
> from other sheets one at a time.
>
> The 19th tab takes totals from the 18th tab
>
> Anyway....I finally got the sheet the way I need it but here is my
> problem. The last 2 tabs are tabs that I need to continually use. The
> way I need to use it is to first compile the sheet with the first 17
> tabs and then copy in the last 2 tabs. The problem is that every time
> I paste in the last 2 tabs, it changes the formulas to include the file
> name of the original sheet. I have tried a million different things and
> can not figure it out.
>
> Hopefully I have explained this clearly enough so that someone can help
> me.
>
> --
> Flipkid2
> ------------------------------------------------------------------------
> Flipkid2's Profile: http://www.excelforum.com/member.php...o&userid=17984
> View this thread: http://www.excelforum.com/showthread...hreadid=569455

--

Dave Peterson

[Gord Dibben]

Before copying the cells with the formulas do an edit>replace.

Edit>Replace

What: =
With: $$$$$ or similar

Replace all.

Copy those over then reverse the process.


Gord Dibben MS Excel MVP

On Tue, 8 Aug 2006 10:52:09 -0400, Flipkid2
<[email protected]> wrote:

>
>Sorry that I only post when I need help, but I am desperate.
>
>I am using a spreadsheet for work. It has 19 tabs on it.
>
>The 18th tab takes numbers from many multiple cells from the first 17
>tabs. Because the cells are not together, when I cut and pasted the
>formulas, they were all out of whack and I had to correct a few hundred
>cells one at a time. Also the first 17 tabs are tabs that are copied
>from other sheets one at a time.
>
>The 19th tab takes totals from the 18th tab
>
>Anyway....I finally got the sheet the way I need it but here is my
>problem. The last 2 tabs are tabs that I need to continually use. The
>way I need to use it is to first compile the sheet with the first 17
>tabs and then copy in the last 2 tabs. The problem is that every time
>I paste in the last 2 tabs, it changes the formulas to include the file
>name of the original sheet. I have tried a million different things and
>can not figure it out.
>
>Hopefully I have explained this clearly enough so that someone can help
>me.

[Flipkid2]

THANK YOU Dave and Gord!!!

You saved me hours of work and headaches!

Thanks again!

[redneck joe]

I use mulitple tabs frequently as well. My issue is on my first (summary) tab, if I have a formula that pulls from:

=EV!C3


when I want to copy/paste that formula in the rest of the column to pull from the other 19 tabs - is there a way to make it know to pull from the next tab? Now, I just paste the formula and change the EV! to the next tabs' name....