Excel Stored Values updating

[billeales]

Hoping someone could help, I have created stored values in excel sheets that I send back to BA. I can get the SV to read, but it only updates in BA when I clear the status cell and trigger it again. Is there a way for it to update in real time without the need to reset the status cell? I was hoping maybe a global command to clear status cells or something within BA to update. I have looked at the VBAs for clearing status cells but am having trouble with those. The file is only used for the SVs so happy for the status cell to clear frequently. Any help would be appreciated.

[billeales]

For anyone looking, this solved it. Thanks to original poster

To overcome the refresh problem, I have the following VBA :

Private Sub Worksheet_Calculate()
If (Range("O9").Value = "PLACED") Or (Range("O9").Value = "ERROR") Or (Range("O9").Value = "OK") Or (Range("O9").Value = "Placing") Then Range("O9").ClearContents
If (Range("O11").Value = "PLACED") Or (Range("O11").Value = "ERROR") Or (Range("O11").Value = "OK") Or (Range("O11").Value = "Placing") Then Range("O11").ClearContents
If (Range("O13").Value = "PLACED") Or (Range("O13").Value = "ERROR") Or (Range("O13").Value = "OK") Or (Range("O13").Value = "Placing") Then Range("O13").ClearContents
If (Range("O15").Value = "PLACED") Or (Range("O15").Value = "ERROR") Or (Range("O15").Value = "OK") Or (Range("O15").Value = "Placing") Then Range("O15").ClearContents
If (Range("O17").Value = "PLACED") Or (Range("O17").Value = "ERROR") Or (Range("O17").Value = "OK") Or (Range("O17").Value = "Placing") Then Range("O17").ClearContents
End Sub

You can then have constantly updating SVs above each column by editing the settings.

Hope this helps.