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

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

--
Rebecca

have a look in help index for OFFSET

--
Don Guillett
SalesAid Software
[email protected]
"Rebecca" <[email protected]> wrote in message
news:[email protected]...
>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
>
> --
> 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


"Don Guillett" wrote:

> have a look in help index for OFFSET
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Rebecca" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> > --
> > 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.
--
Rebecca


"Don Guillett" wrote:

> have a look in help index for OFFSET
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Rebecca" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> > --
> > Rebecca
>
>
>

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
--
Don Guillett
SalesAid Software
[email protected]
"Rebecca" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> Rebecca
>
>
> "Don Guillett" wrote:
>
>> have a look in help index for OFFSET
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Rebecca" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> >
>> > --
>> > Rebecca
>>
>>
>>