Cancelling a formula when a staic value is pulled

**Ian-C** · 09-11-2014, 09:06 AM

Hi,

This is my first post on here so I hope I am in the right area.

Basically I have created a formula using IF statements in Excel 2010 to pick up a value if a particular milestone have been achieved i.e. when 10, 20, 30 etc sales have been achieved pulled from the data source it is looking against bring back the detail in the respective cells.

I have created this formula and its all working fine but I now need to amend it so that when the formula pulls a value (i.e. the cell is no longer blank) it becomes a static value rather than the formula. I know I could simply copy and paste special the values over this but need to automate this process. Basically I need it to be if you have a value (because of the formula) then become the static value but if not keep the formula.

Is this something I could do in visual basic and if so how would I go about doing it? Or could I create a create a mirror image of the worksheet I am working on and say if you don't have a value i.e. a particular milestone has not been achieved, then keep the formula if not save as the static value. If this way can be do what formula would I need for that?

Any help would be very helpful and hope I am clear

Many thanks
Ian

**Richard Buttrey** · 09-11-2014, 09:47 AM

Hi,

It would be easier to comment with certainty if we had the workbook to look at. A cell can only contain one thing at a time, either a formula or a constant. If you have some other variable that indicates when the formula should no longer change then it might be possible to use that in another IF function wrapped around your existing function.

If not then to convert the formula to a value automatically would require an event driven macro. You'd need to decide what that event should be, could be a sheet change event, a particular sheet selection change event or several others, perhaps even a button that you clicked.

I suggest you upload the workbook and show some before/after results and explain how the results are calculated.

**Ian-C** · 09-11-2014, 10:14 AM

Hi Richard

thanks for your reply. This is probably a silly question but how do I go about uploading the workbook?

Thanks
Ian

**newdoverman** · 09-11-2014, 10:41 AM

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 use the paperclip icon to open the upload window.

View Pic

**Ian-C** · 09-11-2014, 11:55 AM

Hello,

I have uploaded a sample workbook. Basically I need a macro to say if the formula which is in the cells picks up a set of new values then no longer be a formula but instead become a static value but the result of the formula in the worksheet.

The Before worksheets show this i.e. there are formula in all the appropriate cells and the data values are less than the specified cell values for the formula but the AFTER worksheets show the formula still where the criteria can not be met but crucially also the 'hard keyed' static values which was pulled using the formula.

Thank you

**Richard Buttrey** · 09-11-2014, 12:07 PM

Hi,

It would be easier to comment with certainty if we had the workbook to look at. A cell can only contain one thing at a time, either a formula or a constant. If you have some other variable that indicates when the formula should no longer change then it might be possible to use that in another IF function wrapped around your existing function.

If not then to convert the formula to a value automatically would require an event driven macro. You'd need to decide what that event should be, could be a sheet change event, a particular sheet selection change event or several others, perhaps even a button that you clicked.

I suggest you upload the workbook and show some before/after results and explain how the results are calculated.

**Ian-C** · 09-11-2014, 12:34 PM

Just to clarify the after results are based on the formula which were in the cells in questions but as the values of the cells meet the criteria (of the formula) the cells change from being formula to static value whilst the rest of the formula in the worksheet/worksheet remain

Thanks

**Richard Buttrey** · 09-11-2014, 12:37 PM

Sorry, but that does nothing to clarify - at least to me.

A picture is worth a thousand words so please upload the workbook with some manually created results and notes explaining how you have arrived at the results.

**Ian-C** · 09-11-2014, 12:52 PM

Please see an attached example of the worksheet/macro I need help on

Cancelling a formula when a staic value is pulled

LinkBack

Thread Tools

Rate This Thread

Display

Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Re: Cancelling a formula when a staic value is pulled

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Staic Date Stamp for adjacent cell being equal to "Dormant"

auto insert staic date with batch/export of data

[SOLVED] InputBox and cancelling

Cancelling Hyperlinks

[SOLVED] Cancelling a macro

Bookmarks

Bookmarks

Posting Permissions