save just data, no forlumas

[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

 

[Dave Peterson]

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.