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.)

 

[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

 

[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....