Automate a cell to change from a formula result to the value only

[Bobbo Jones]

Hi all, I am trying to create a formula that records weekly results of my balance sheet, for example my net profit.
I am hoping to track my business over 52 weeks and graph the results to find patterns. My computer is on all the time, and I have a lot of things automated, but I am often unable to copy this data down manually at regular intervals.

I already have a formula which can return the 'net profit' result on a separate sheet at a certain time every week. The problem is that being a formula, it continues to update every time my net profit changes, rendering the result useless.

What I need is another formula which can take that result, and turn it into a value only. I was thinking something along the lines of an IF function.

For example,
Cell C2 has the date based formula that records the net profit at a set time,
C3 has an IF function. Something along the lines of, IF(FALSE,0,awesome function that copies the result and makes it a value)

is there a way to do this at all?
Thanks all

[JohnTopley]

You will need VBA to do this.

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Richard Buttrey]

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Bobbo Jones]

Title updated. I posted late at night and forgot to finish the title.

Thanks for your response John, I've attached an attempt at an example workbook.
For simplicities sake I have changed the goal from 'net profit' to 'Total Sales'

B3 has the start date which was manually entered. The rest of B column has an IF function that triggers when TODAY is greater than 7 days from the cell above.

The C column has an IF formula that triggers when the adjacent B cell is no longer FALSE or an error.
It reveals the 'Total Sales' of that moment. All cells above that also have this formula will reveal the identical result, which is where my problem lies.

Column D is where the result I want will go. Cells D 3&4 I have manually added up and entered, simply to pose what I would like the results to look like.
Cell D5 is an IF function that is currently revealing the if true scenario. I want it to take the $917 currently shown in cell C5, and convert it to the value only.
I want it to trigger only when that IF function it is within tells it to.

Keep in mind that on the 9th of Jan these cell references will change due to the date dependant formula.
I am content with all the error's but if a neat fix is known for that let me know! I had an if error at the start but the following formulas were getting confused by it as it gives a Boolean result.

I hope this is clear?? Let me know if I can clarify anything
Thanks everybody!

[mehmetcik]

Right Click on the sheet name at the bottom of excel and select view code.

A macro module will open.

Paste this code there and close it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.Column <> 3 Then Exit Sub
Target.Offset(0, 1).Value = Target.Value
End Sub

[Bobbo Jones]

Thanks..
Is there any possible work around that does not require VBA?