save just data, no forlumas

  • Thread starter Thread starter trav
  • Start date Start date
T

trav

So with the help of this forum, i have proceeded to make many forumlas
and macros to make my life easier. I am now running into the problem
that there are so many formulas that it is becoming slow and the file
size is increasing. After adding my last formulas
changed
=IF(ISNA(VLOOKUP(A4,Main!$A$2:$C$2000,3,FALSE)),"",VLOOKUP(A4,Main!$A$2:$C$2000,3,FALSE))

to
=IF(ISNA(INDEX(Sheet3!$E$2:$E$5000,MATCH(1,($A2=Sheet3!$D$2:$D$5000)*(F$1=Sheet3!$B$2:$B$5000),0))),"",INDEX(Sheet3!$E$2:$E$5000,MATCH(1,($A2=Sheet3!$D$2:$D$5000)*(F$1=Sheet3!$B$2:$B$5000),0)))

I realized that my file size nearly doubled. because i put this forumla
in hundreds of cells.

I was wondering if there was a way to save an excel workbook as values
only and not formulas, i know you can save it as text, and cvs, and
other types, but i was hoping i could still save it as an XLS but with
no formula's only values.


Any ideas
 
You can always select the range with formulas (or all the cells on that sheet)
and edit|copy followed by edit|paste special|Values.

But I'd save it as a new name--just in case you want those formulas back.

And you could shrink your second formula a bit:

=if(isna(MATCH(1,($A2=Sheet3!$D$2:$D$5000)*(F$1=Sheet3!$B$2:$B$5000),0)), ...

But it's not much shrinkage.

If you're returning lots of values based on that match in both columns, you
could separate it into a couple of different cells:

Say in Q2:
=MATCH(1,($A2=Sheet3!$D$2:$D$5000)*(F$1=Sheet3!$B$2:$B$5000),0)

Then to get each value:
=if(isna(q2),"",index($e$2:$e$5000,q2))

That way you're only looking for that match once per row.
 
Back
Top