Formula to show text instead of last known data

[Catsonheat]

Hi everyone,

I have a Countif formula which is counting data from another workbook. I have a macro which when pressed will open that other workbook and the Countif then updates with this new information.

The problem I have is that once I have loaded the separate workbook and updated my formulas, even after closing the separate workbook down, the data remains where what I am hoping for it to do is then revert to a Nil status or display text such as "run report" or "load workbook" etc.

The problem is I have other formula which should have notifications and warnings to detect when there has been an error or change in certain cells however as the last known data from the Countif gets stuck in the sheet, it does not detect there has been a change.

I hope this makes sense. I am fairly new to excel and currently only have access to my phone as my laptop won't connect to the Internet to copy my formulas in.

I will copy the details of my formula if required tomorrow. If the above makes sense does anyone know if this can even be done?

Kind regards

[dflak]

Let me restate what I think you are doing to see if I understand the issue properly. You open workbook A and it has a countif to workbook B. You open workbook B and the countif updates. You close workbook B manually and your expectation is that the countif be zeroed out automatically.

There is code available that checks to see if a workbook is open. Would it work for you if you added a second button that checks to see if workbook B is open. If it is, do nothing. If it isn't zero out the value and re-establish the formula.

[Catsonheat]

Let me restate what I think you are doing to see if I understand the issue properly. You open workbook A and it has a countif to workbook B. You open workbook B and the countif updates. You close workbook B manually and your expectation is that the countif be zeroed out automatically.

There is code available that checks to see if a workbook is open. Would it work for you if you added a second button that checks to see if workbook B is open. If it is, do nothing. If it isn't zero out the value and re-establish the formula.

Hi,

Thats exactly right! I will be able to post the formulas to show what my formula is doing shortly once i get home.

So ideally i should open workbook A and there should be no data/"0"/"Run Report" etc. It should advise that workbook B needs running. Once workbook B is loaded it will countif and then update. If i then close workbook B it should revert to its original state as it cant countif without workbook B being open.

Your option mentioned might work. Would i be able to build that into the Macro for loading Workbook B originally or would it need to be a seperate macro? (Im still very new and my experience/skills with macros is very limited)

Thanks for your response!

[dflak]

See if this does it for you. You can adjust most of what you need to fit your spreadsheet in the Initialize Variables section. The only other part of the code you will have to change is the COUNTIF formula.