Any Excel pros out there?

Question

I finally finished a spreadsheet that will look at another spreadsheet with a lot of detailed sales figures, look at a cell that is TODAY(), find that row on the other spreadsheet, and populate the page with data from that row.

My problem is that they like to have each day's sheet saved. How do I save a spreadsheet that just has the data in the cells, but ignores the formulae that generated those data.

That is to say if a cell is ='[Current DSR.xls]Berkeley 101'!$B$26*'[Current DSR.xls]Berkeley 101'!$G$26, and that equals "20", or =+SUMIF('[Current DSR.xls]Berkeley 101'!$B$26:$B$64,C$2,'[Current DSR.xls]Berkeley 101'!$G$26:$G$64) comes up as $1200, how do I make it save so the next time it is opened 20 and $1200 shows up and it doesn't bother looking to the other spreadsheet?

There is two problems with the way I designed the sheet. The first is that the date that all the cells reference is "today()", so when it is opened at a later date, it will calculate the new values. The other problem is that it will only calculate data if the other document it is getting values from is open at the time. Since it's based on a sheet called "Current DSR", and past months are saved as "JULY08 DSR" "MAY07 DSR" that will make people unable to see anything older than a month anyway.

Answer 1

For your first part, I would just copy and paste special->values to "value out" your sheet once you are done with it. You could record yourself doing it in a macro and then tie the macro to a button as well for ease and slickness. Beyond that you are going to get into linking issues and inconsistencies, and it's just not worth having to research things when they go wrong.

For your TODAY() issue, instead of using TODAY why not use a cell than you type in the date? Just remember to always key today's date when you open the sheet. If you wanted to go back to, say, yesterday, just key that date. Then point your formulas to that cell instead of the TODAY function.

Answer 2

If you want to convert everything on a given sheet from formulas to 'paste special' values, you can do it with a VBA macro quite easily.  

Open your workbook

Copy this macro to the clipboard:

Sub Finalize_Sheet()

For Each cell In ActiveSheet.UsedRange

cell.Select

If ActiveCell.HasFormula Then

ActiveCell.Value = ActiveCell.Value

End If

Next

End Sub

Next, press ALT + F11

Insert  >  Module

Paste the macro into the Module area to the right.

Close back to Excel.

Go to Tools  >  Macro  >  Macros

Highlight the Finalize_Sheet macro, if it is not already highlighted.

Click: Options

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

To run the macro and convert all formulas to the values generated by the formulas, press CTRL + your shortcut letter.

Answer 3

use the paste as special option

Answer 4

Copy all of the info then paste special and click value only.