Excel 2007 : Copy and pasting values only

[benrollo13]

Hi all

First time on here and I really hope someone can help me out!

I am putting together a spreadsheet that will be filled in on a daily basis. Roughly speaking, this spreadsheet is used to track the number of processes my team get through on a daily basis. For trend analysis purposes, each day's data needs to be stored.

At present, I have a worksheet which tracks the number of processes each hour of each day (called Tracker). It is linked to an overview sheet (called Overview) which has all of the raw data in it. I enter the date on this Overview sheet, and the data is then pulled through to the relevant place in the Tracker sheet by using the IF formula to determine the active day. The formula looks like this:

=IF(Overview!$E$1=40909,Overview!$K$8,"")
So, basically if the date (cell E1) is 01/01/2012 (40909 in Excel lingo), then this particular cell pulls through the value from K8. If the date does not match 40909, the cell is blank ("").

This formula works great for this purpose. But when it comes to the end of the day, I need to manually overtype that day's numbers so that they do not revert back to blank cells when I change the date in the Overview sheet. This is where (hopefully) someone comes to my rescue:

Is there a way - either with a formula, C&P function, or a macro - to automatically copy and paste values ONLY where a value is present in a cell? All of the blank cells with formulas in need to stay as they are. This would save quite a bit of time on a daily basis if this can be done automatically.

Any and all help would be much appreciated! I haven't attached the spreadsheet because it does have some delicate information, but if it would help to make my question clearer I could have a play around and extract some of the data.

Many Thanks

Ben

[JBeaucaire]

1) Highlight the column of Values you wish to "flatten", or the range of cells in that column
2) Copy
3) Edit > Paste Special > Values

Done.

[benrollo13]

Thanks for the reply JBeaucaire. Unfortunately, pasting values like this basically takes all of the formulas out of the cells and replaces them with the blank cell that the formula is set up to return. I need to find a way of pasting values only where a value is showing in a cell - keeping the formulas in the background

[JBeaucaire]

If A2 shows a value (result of a formula) you want to remove the formula?
If A3 shows a value of "blank", you want to leave the formula in the cell for now?

...etc down the column?

You could apply a custom AUTOFILTER to that column to display values >0 then copy>PasteValues.

[benrollo13]

That's bang on - apologies if I was confusing with my terminology! I'm going to have a fiddle around with filters - it's going to be tricky because there are about 100 separate sets of data that are all going to need to be filtered individually! I will report back with my findings!

[JBeaucaire]

You could record a macro of your turning on and applying a filter to the data on one sheet. Then try bringing up another sheet an running that macro, might work as is for you.