Deleting Rows but not Formulae

C

cefpe

I have a worksheet with 200 rows and 50 columns of data with formula
in some of the cells. Each month I have to delete some of the rows. I
I delete a row the data base moves up and I lose the formulae.
How can I delete rows (or the information) without the above problem?
Thanks,
C
 
C

cefpe

Frank,
I realize that I can go back and copy the formula but I thought ther
may have been an easier way.
Thanks!
C
 
F

Frank Kabel

Hi
what I meant was that you may post your existing formula to this
newsgroup so it is easier for 'us' to make a specific suggestion how to
change it :)
 
C

cefpe

Hi,
There are many fornulae in each row of the worksheet. Unless I a
missing something how can I change the formula so it is not delete
when I delete data in the rows above that is not needed?
If I have 500 rows and delete 50, my DB now has 450 rows of formula
that have to be replaced or copied from the cells above.
C
 
F

Frank Kabel

Hi
still it would be easier if you at least post ONE example formula. But
if you have something like
=A1*B2
you could replace it with
=INDIRECT(A1)*INDIRECT(B2)

Also nthe use of OFFSET could solve your issue. But again a more
specific answer would be easier using one of your formulas as there're
other techniques as well (depending on your formulas)
 
G

Gord Dibben

Maybe don't delete the row, just delete the data?

Edit>Go To>Special>Constants. Use the checkboxes to select what you want to
get rid of and OK out.

Now with those cells selected, Edit>Clear>Contents

Gord Dibben Excel MVP
 
C

cefpe

Gentlemen,
Below is one of the of the formulae in the worksheet that ha
protection.
IF(ISERROR(VLOOKUP($A4,EMPLDB!$A$2:$D$2,FALSE))=TRUE))=TRUE,0 and th
same expression on the left is repeated. All of the formulae are lon
and most refer to another worksheet or database, although there ar
some that are simple and add columns or rows.
I'll also try the edit, goto, special constants.
Thanks,
C
 
C

cefpe

Gord,
I tried your solution but that method deletes all constants.
My database has employees who may have quit or been terminate
(construction sites). These employees and their data need to be erase
from the DB each month. There may be 150 to 200 rows and each month 1
to 20 rows need to be deleted otherwise the DB becomes cluttered an
too large.
C
 
Top