Formula to change calculated results to values

[Cameron]

Here's the situation; I'm a high school soccer coach and want to record
player game stats on one sheet for a given game & date, and have that same
data appear on a different sheet for that given date and game. I can
reference the game data sheet cells and it works fine, but, when I clear the
contents for the next game, the data in the destination sheet clears as well,
(I need to retain that data).

I use as a sheet named (GameStat) where I enter shots, assists, etc for
Player "A" for a given game and date. example: col's E through P (cell 5) on
a given date for a given player.

then in a second sheet (PlayerStats) I want to keep the Individual player's
game stats for that date.

I'm Using the GameStat sheet for multiple games and every time I clear the
previous entries from the preceeding game, the results in the "PlayerStats"
page is cleared as well,....I need that data to be retained in the
PlayerStats sheet, How?

Thanks

 

[Don Guillett]

To change formulas to values use copy>edit>pastespecial>values
or add the paste values icon to your toolbar( it is a small box with 12 on
it)

 

[Cameron]

Don, thanks for your tip, but I'd like to have it done automatically using a
formula in the cells themselves instead of the tool bar buttons.

 

[Don Guillett]

Then you need a macro
sheets("sheet1").range("d1:d4")=sheets("sheet2").range("d1:d4").value

 

[Arvi Laanemets]

Hi Cameron

Replacing formulas with their values is changing cell entries. In excel
function's can't change anything, they only can return a value acording
their parameters. So changing formulas with values by function is
impossible. You have to do this manually, or you have to write a procedure,
which is started pressing button or hot-key, or from some worksheet event.

Arvi Laanemets