Can I tell if a cell is being edited?

[MOB]

My client's accounting system has an Excel link to upload transactions using OLE. Generally works well, but I have one really annoying issue!!

The concept is that a cell (eg B1) has a custom formula in it which contains the entries to be transferred across. Typing "Yes" in upload cell A1 sends the data into the accounting system. Typing "No" into upload cell A1 disables the OLE system.

If you typed Yes into cell A1, and forgot to then change it to No, each time you edit any cell in Excel, it will transfer the entry repeatedly.

My issue is that I am typing Yes, then immediately typing No, and in some instances it is repeatedly (2-4 times) transferring the data.

I have been advised that "if the Upload cell is set to YES, then every time you edit ANY CELL in the spreadsheet it will upload another NOM".

So my question is, what could be happening between typing yes and no in the upload cell that could be construed as editing? It is literally a couple of seconds between Yes/No but obviously something is happening!

We use this functionality on other spreadsheets and do NOT have this issue there.

Any pointers or tips would be much appreciated!

TIA

 

[RoryA]

Wild guess - do you have any volatile functions in that workbook, such as OFFSET, INDIRECT, TODAY, NOW?

 

[MOB]

mmmmmmmm............I use a LOT of INDIRECT formula on that spreadsheet, but not on the "ok" spreadsheets....................bingo!

What should I do ?????

Bear in mind I use these INDIRECTS, understand the formulae but I don't have the in depth understanding that you guys have of what happens "behind the scenes"!

 

[RoryA]

INDIRECT is volatile so it will recalculate whenever you change anything. It may be that that is causing your problem, it may not.

 

[MOB]

I shall remove them and report back...........thanks

 

[MOB]

Just to confirm, removing the INDIRECT formulae resolved my issue entirely.

Many thanks for your help, much appreciated!

 

[RoryA]

Glad to help.