Convert Values to formulas

what is the reltionship between the cells with 123 and 3 in them,ie is it
consistent through out
--
paul
remove nospam for email addy!



"TSter" wrote:

> I am running excel 2003. In general terms, I have a cell that has a value of
> 123 in it. I want to be able to copy another cell that has a value of 3 in
> it and do some sort of "Paste Special-Formula-Add" so that the cell will now
> show "=123+3" in it. if the cell already has =123 in it, then I can get it
> to work, but it only has 123 in it so I end up with a new cell that has 126
> in it. The problem is that I need to do this with thousands of cells and I
> need to be able to do this so that I have an audit trail and that I know that
> the original value was 123 and that I added 3 to it. Can anybody think of a
> way that I can get this to work? I need to be able to automatically insert
> an equal sign in every cell or have my paste funtion do this for me. Any
> thoughts? I've tried everything i can think, except for a special macro.

I'm not sure I understand the question. Let's say I have 3 rows by 12
columns. I have to adjust each value in each cell by a different amount. I
have a spreadsheet with the original data that has the 123 values (obviously
there all different values). I have a second spreadsheet that has calculated
how much each of the 36 cells should change by. This sheet has the 3 values
in them. I want to copy the change 36 cells in the change sheet and do a
paste special add on the original and have the original file now show =123+3,
not 126. Make sense?

"paul" wrote:

> what is the reltionship between the cells with 123 and 3 in them,ie is it
> consistent through out
> --
> paul
> remove nospam for email addy!
>
>
>
> "TSter" wrote:
>
> > I am running excel 2003. In general terms, I have a cell that has a value of
> > 123 in it. I want to be able to copy another cell that has a value of 3 in
> > it and do some sort of "Paste Special-Formula-Add" so that the cell will now
> > show "=123+3" in it. if the cell already has =123 in it, then I can get it
> > to work, but it only has 123 in it so I end up with a new cell that has 126
> > in it. The problem is that I need to do this with thousands of cells and I
> > need to be able to do this so that I have an audit trail and that I know that
> > the original value was 123 and that I added 3 to it. Can anybody think of a
> > way that I can get this to work? I need to be able to automatically insert
> > an equal sign in every cell or have my paste funtion do this for me. Any
> > thoughts? I've tried everything i can think, except for a special macro.

the only way i can think to do it is make a new text array.
In a cell on the second sheet type ="="&A1&"+"&A2,copy down and or across to
make a new text array copy and paste back into the original sheet.This
assumes the original value is in a1 and the change value is a2.Probably best
to paste this in an adjacent array so as to not overwrite actual calculation
values?
--
paul
remove nospam for email addy!



"TSter" wrote:

> I'm not sure I understand the question. Let's say I have 3 rows by 12
> columns. I have to adjust each value in each cell by a different amount. I
> have a spreadsheet with the original data that has the 123 values (obviously
> there all different values). I have a second spreadsheet that has calculated
> how much each of the 36 cells should change by. This sheet has the 3 values
> in them. I want to copy the change 36 cells in the change sheet and do a
> paste special add on the original and have the original file now show =123+3,
> not 126. Make sense?
>
> "paul" wrote:
>
> > what is the reltionship between the cells with 123 and 3 in them,ie is it
> > consistent through out
> > --
> > paul
> > remove nospam for email addy!
> >
> >
> >
> > "TSter" wrote:
> >
> > > I am running excel 2003. In general terms, I have a cell that has a value of
> > > 123 in it. I want to be able to copy another cell that has a value of 3 in
> > > it and do some sort of "Paste Special-Formula-Add" so that the cell will now
> > > show "=123+3" in it. if the cell already has =123 in it, then I can get it
> > > to work, but it only has 123 in it so I end up with a new cell that has 126
> > > in it. The problem is that I need to do this with thousands of cells and I
> > > need to be able to do this so that I have an audit trail and that I know that
> > > the original value was 123 and that I added 3 to it. Can anybody think of a
> > > way that I can get this to work? I need to be able to automatically insert
> > > an equal sign in every cell or have my paste funtion do this for me. Any
> > > thoughts? I've tried everything i can think, except for a special macro.

This did accomplish what I wanted! Thanks! Follow up though. I can copy
this text cell to another cell doing a Paste Special-Value. Do you know how
I can easily convert this cell from text back to an excel formula? I can
click in the cell and hit enter and it will do this, but I would need to do
this with thousands of cells. Can you think of any way to do this to a whole
selection of cells?

"paul" wrote:

> the only way i can think to do it is make a new text array.
> In a cell on the second sheet type ="="&A1&"+"&A2,copy down and or across to
> make a new text array copy and paste back into the original sheet.This
> assumes the original value is in a1 and the change value is a2.Probably best
> to paste this in an adjacent array so as to not overwrite actual calculation
> values?
> --
> paul
> remove nospam for email addy!
>
>
>
> "TSter" wrote:
>
> > I'm not sure I understand the question. Let's say I have 3 rows by 12
> > columns. I have to adjust each value in each cell by a different amount. I
> > have a spreadsheet with the original data that has the 123 values (obviously
> > there all different values). I have a second spreadsheet that has calculated
> > how much each of the 36 cells should change by. This sheet has the 3 values
> > in them. I want to copy the change 36 cells in the change sheet and do a
> > paste special add on the original and have the original file now show =123+3,
> > not 126. Make sense?
> >
> > "paul" wrote:
> >
> > > what is the reltionship between the cells with 123 and 3 in them,ie is it
> > > consistent through out
> > > --
> > > paul
> > > remove nospam for email addy!
> > >
> > >
> > >
> > > "TSter" wrote:
> > >
> > > > I am running excel 2003. In general terms, I have a cell that has a value of
> > > > 123 in it. I want to be able to copy another cell that has a value of 3 in
> > > > it and do some sort of "Paste Special-Formula-Add" so that the cell will now
> > > > show "=123+3" in it. if the cell already has =123 in it, then I can get it
> > > > to work, but it only has 123 in it so I end up with a new cell that has 126
> > > > in it. The problem is that I need to do this with thousands of cells and I
> > > > need to be able to do this so that I have an audit trail and that I know that
> > > > the original value was 123 and that I added 3 to it. Can anybody think of a
> > > > way that I can get this to work? I need to be able to automatically insert
> > > > an equal sign in every cell or have my paste funtion do this for me. Any
> > > > thoughts? I've tried everything i can think, except for a special macro.

Just in case someone comes across this years afterward like I did, there is a trick to get this to update correctly after pasting the values.

So add a ? before the equals sign in your text string.

Now, just as the OP said, he did a Paste Special and choose Values. Now you should have a bunch of cells with formulas showing in all of them. Select them all and Ctrl+H (Find and Replace) and Replace all your ? signs with nothing. All the cells should update according to the formula.