A few things - I have a basic background with excel and inputting formulas
from statistics classes and also have done a basic C++ programming course.

I have set up a spreadsheet as a template in excel that I can input raw data
into from my research and get out numbers I need to tell me whats important.
Here are the few things I have run into:

1. The first template I made is only 2 spreadsheets - 1 for the control data
and another that I can make copies of that compares itself to the control
data. The problem is that even the template is giant (~75MB) for some reason
even though I am not using more than 20 rows and no more than 15 columns. The
spreadsheets go down ~65K rows and I've been trying to delete those rows
thinking all those empty cells are the cause of this monster of an empty
excel file. (this also causes our labs computer to lock up for several
minutes when opening/saving/closing). If anybody has any suggestions on how
to remedy this it would be greatly appreciated. (the selecting all and right
click delete thing just kills the program.)

2. In a second ditch effort to fix this I have begun transferring the few
data files I have run though the template (they have ~15 spread sheets) into
new clean files by just copying and pasting the areas that include all of the
data. When I put them into the new files though they reference the old giant
files in their formulas - meaning I can't delete them. Is there anyway to
make it so that when you copy from one spreadsheet to another it doesn't
automatically reference spreadsheets in the old file but instead references a
spreadsheet in the new file with the same name that I make (since I'm copying
them over)?

Thank you for all of your help.

Also if anybody has any suggestions on where I can learn about how to write
and run basic Macros or even just some more advanced formula writing in excel
I would also appreciate it. (I'm really just familiar with using $ to keep
cells straight and being able to select formulas.)

1) See Debbie's macro for cleaning up the worksheet used area at
http://www.contextures.com/xlfaqApp.html#Unused

2) If I have =SUM(A1:A10) in A11 of Book1 and copy A1:A11 to Book2, the SUM
refers to the new book
How are you doing the copy & paste?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"alandh" wrote in message
...
A few things - I have a basic background with excel and inputting formulas
from statistics classes and also have done a basic C++ programming course.

I have set up a spreadsheet as a template in excel that I can input raw
data
into from my research and get out numbers I need to tell me whats
important.
Here are the few things I have run into:

1. The first template I made is only 2 spreadsheets - 1 for the control
data
and another that I can make copies of that compares itself to the control
data. The problem is that even the template is giant (~75MB) for some
reason
even though I am not using more than 20 rows and no more than 15 columns.
The
spreadsheets go down ~65K rows and I've been trying to delete those rows
thinking all those empty cells are the cause of this monster of an empty
excel file. (this also causes our labs computer to lock up for several
minutes when opening/saving/closing). If anybody has any suggestions on
how
to remedy this it would be greatly appreciated. (the selecting all and
right
click delete thing just kills the program.)

2. In a second ditch effort to fix this I have begun transferring the few
data files I have run though the template (they have ~15 spread sheets)
into
new clean files by just copying and pasting the areas that include all of
the
data. When I put them into the new files though they reference the old
giant
files in their formulas - meaning I can't delete them. Is there anyway to
make it so that when you copy from one spreadsheet to another it doesn't
automatically reference spreadsheets in the old file but instead
references a
spreadsheet in the new file with the same name that I make (since I'm
copying
them over)?

Thank you for all of your help.

Also if anybody has any suggestions on where I can learn about how to
write
and run basic Macros or even just some more advanced formula writing in
excel
I would also appreciate it. (I'm really just familiar with using $ to keep
cells straight and being able to select formulas.)

I know that when copying formulas that refer to the same sheet from one book
to another they still refer to themselves, but when (ie) worksheet1 from
book1 that is referring to worksheet 2 in book1 to find a sum, when I copy
worksheet1 to book2 the formulas change to refer to worksheet 2 in book1 - is
there anyway I can change the formula in worksheet 1 book1 so that when I
copy and paste it into book 2 it still will refer to a worksheet with the
same name in the same book rather than the old book?

"Bernard Liengme" wrote:

1) See Debbie's macro for cleaning up the worksheet used area at
http://www.contextures.com/xlfaqApp.html#Unused

2) If I have =SUM(A1:A10) in A11 of Book1 and copy A1:A11 to Book2, the SUM
refers to the new book
How are you doing the copy & paste?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"alandh" wrote in message
...
A few things - I have a basic background with excel and inputting formulas
from statistics classes and also have done a basic C++ programming course.

I have set up a spreadsheet as a template in excel that I can input raw
data
into from my research and get out numbers I need to tell me whats
important.
Here are the few things I have run into:

1. The first template I made is only 2 spreadsheets - 1 for the control
data
and another that I can make copies of that compares itself to the control
data. The problem is that even the template is giant (~75MB) for some
reason
even though I am not using more than 20 rows and no more than 15 columns.
The
spreadsheets go down ~65K rows and I've been trying to delete those rows
thinking all those empty cells are the cause of this monster of an empty
excel file. (this also causes our labs computer to lock up for several
minutes when opening/saving/closing). If anybody has any suggestions on
how
to remedy this it would be greatly appreciated. (the selecting all and
right
click delete thing just kills the program.)

2. In a second ditch effort to fix this I have begun transferring the few
data files I have run though the template (they have ~15 spread sheets)
into
new clean files by just copying and pasting the areas that include all of
the
data. When I put them into the new files though they reference the old
giant
files in their formulas - meaning I can't delete them. Is there anyway to
make it so that when you copy from one spreadsheet to another it doesn't
automatically reference spreadsheets in the old file but instead
references a
spreadsheet in the new file with the same name that I make (since I'm
copying
them over)?

Thank you for all of your help.

Also if anybody has any suggestions on where I can learn about how to
write
and run basic Macros or even just some more advanced formula writing in
excel
I would also appreciate it. (I'm really just familiar with using $ to keep
cells straight and being able to select formulas.)

You'd need to use editreplace. In the source book, editreplace the formula
cells': = (the equal sign) with say: zzz. Then copy n paste these over in the
destination sheet in the other book. Then reverse it with editreplacing the
"zzz" with: = , which will re-activate the formulas.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"alandh" wrote:
I know that when copying formulas that refer to the same sheet from one book
to another they still refer to themselves, but when (ie) worksheet1 from
book1 that is referring to worksheet 2 in book1 to find a sum, when I copy
worksheet1 to book2 the formulas change to refer to worksheet 2 in book1 - is
there anyway I can change the formula in worksheet 1 book1 so that when I
copy and paste it into book 2 it still will refer to a worksheet with the
same name in the same book rather than the old book?