Copying a formula result

**Dave_A** · 02-27-2007, 09:25 AM

hey guys this seems to be a great forum...

im looking for some help with copying a formula. Basically i want to paste special a formula result automatically.

I have a if function set up so when 2 days corespond then in the 3rd cell it records the value, however when ever the date changes the value disappears, i can keep the value u there by paste special but i would love to set this up as an automatic thing once a value is recorded it records the value and deletes the formula, or the value doesnt change when the date (thus the condition) changes...

if someone could give me some guidence on this i would be estatic, i have spend a few hours on it and its driving me up the wall

cheers

**Carim** · 02-27-2007, 09:28 AM

Hi,

There are several possibilities depending on what suits you best ...
a simple event macro could handle it automatically for you ...
Do you mind uploading a zipped copy of your worksheet ...?

**Dave_A** · 02-27-2007, 09:45 AM

cell aj30 changes every day with an external update query, in sheet 2 i have the if function, however everytime AJ30 changes, i want the value which is there to stay there and not move to the false (or 0)

cheers

**Carim** · 02-27-2007, 10:39 AM

Hi,

See attached ...

**Dave_A** · 02-27-2007, 10:54 AM

would you be able to explain how you did that for me as i need to create that for some of the other numbers on that sheet as well

edit:
i just tried to test it out,and maybe i have to do something, but when i change the date manually in ak30 it does change the value on the sheet 2, however if i then change the date from (say 27th to 28th) the value for the 27th figure just goes back to false, am i doing something wrong??

im only using office xp if thats an issue??

**Carim** · 02-27-2007, 11:07 AM

Hi,

The value cannot go back to False ... since the formula which triggers false is automatically replaced by its result ... see event macro ...

**Dave_A** · 02-27-2007, 11:14 AM

where do i find the event macro from??? i checked under macros in tools and there is nothing in there

is an event macro a different type of macro than a normal one?

**Carim** · 02-27-2007, 11:24 AM

Go to sheet 2
Go to worksheet tab
Right Click
Select view code ...

**Dave_A** · 02-27-2007, 11:32 AM

ah yeah i do see the code, i have no idea what it means but i do see it

however when i do change the date it is still coming back up false where the data was and then making where to new statement correct with the number...

is there any other way to do this apart from a macro

sorry if im being a pain...

**Dave_A** · 02-27-2007, 08:28 PM

ok the issue was i actual had disabled macros when i opened the file.. so thank you very much it seems to be working now...

now im just wondering is there any way which i can now just copy something so i can make it work for other numbers and sheets as well??

edit:
ok ive just gone through and copied the code and placed it in each new sheet, and its working perfectly now... im so appreciative...

thank u very much... uve made my day...

**Carim** · 02-28-2007, 03:50 AM

Glad you could fix your problem ...

Thanks for the feedback

**Dave_A** · 03-07-2007, 10:00 AM

just to bring back an old topic

my spreadsheet has got a bit more complicated and now i have 2 column of data in the same sheet run by formulas... im finding the 1st column is being copied great, however its just the 2nd column is not copying accross so when the day moves on it deletes the data... in the file it shows the last number in each column still has the formula, where the 1st one only has the number

would i stop this by doing an addtional copy of the macro per sheet, or do charcaters need to be changed

ive uploaded the sheet if it helps and each sheet showing im having the problems with

cheers guys

**Carim** · 03-07-2007, 11:34 AM

Hi,

There is no attachment ... but I can guess ...
Replace code by following ...

Please Login or Register  to view this content.

**Dave_A** · 03-14-2007, 09:52 PM

im still having a few problems with this one sorry

the TLSCA sheet is still not copying correctly from the TLSCA Historic.

It is column E which i need copied ...

Could someone please help me again.

Thanks

(ill make sure i attach the file correctly)

**Carim** · 03-15-2007, 02:26 AM

Hi,

Please Login or Register  to view this content.

**Dave_A** · 03-22-2007, 09:13 AM

Carim, im still having some problems with this code...

it seems to be doing its job in replacing the formula nice and well but the problem is next time i open the document and the next days data comes through, it is deleting the information from the previous day (when its only a figure not a formula)

Is there something i can do to stop this?

its happening on all sheets shown in the document, its only the formulas that link to the hide sheets i want to copy from..

If you need more detail just let me know.

Cheers

File Here for Reference

Copying a formula result

LinkBack

Thread Tools

Rate This Thread

Display

Copying a formula result

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions