I need formula help or create a macro to copy and paste value only

[Rebecca]

I need to have values retained in cells and deactivate formula after use so
the subsequent formula can access same criteria cell for new information only
to be placed in new cells.

Steps:

Report downloaded each month onto Spreadsheet entitled 1.

Title page (Natl Rank) for report reads current Month.
=IF('Natl Rank!$C$1="Month of March 06",'1'!$C$26."")
Enters value in cell based on C1 text reading "Month of March 06"

Cells directly to theright of the March cells are April cells.
Following month same cell Natl Rank C1 text gets changed to read Month of
April
=IF('Natl Rank!$C$1="Month of April 06",'1'!$C$27."")

March column goes blank. April column fills in

I need the data collected for March to be retained somehow but the report
that this data is coming from is over written each month. New month's data
is to be placed in cells next to the preexisting March data. If I change the
text to read Month of April from March my cells for March go blank. When I
overwrite the existing report for the new months data, the data inside the
March cells change and shouldn't, --

Should I create a macro to copy the March data before the new download and
Paste Special the values? If I do that then I am wiping out my formulas I go
but get to retain the data.
Rebecca

 

[Don Guillett]

have a look in help index for OFFSET

 

[Rebecca]

Offset doesn't seem to pertain. I am not trying to calculate rows and
colums. I am trying to maintain a value that is pulled in by a formula. The
problem is the source data changes each month and the new information should
go into other cells. I need a formula that will automcatically pull
information in and make it resident on that cell so that when I download the
new information the next month it won't get copied over.

My formular tell excel (if cell reads X month & year, then collect
information from X cell and place it here) I will not only download the new
information onto the old but will change the Month name in the X cell. So if
that formula is still active it will now see that it does not read (March 06)
anymore and will place blanks in those end cells.

Does this make sense. I am so sorry, I know it's confusing

 

[Rebecca]

What I kind of want to say is now that I have the formula to get the right
information based on the right criteria, is there a way to make it perform as
a one time use, kind of thing. I need it (the formula) turned off after it
pulls the information in one time so the information that it collects remains
in those cells as hard values and the formula never pulls anything else after
that. Since my information on the report that this formula is getting it's
data from will change I don't want it to pick up on any new information.

 

[Don Guillett]

You can

copy>paste values (use the available icons)
or
have a macro that does the calculation and just leaves the values

Sub makeformula()'example
Range("a12") = Range("e2") * 22
End Sub