Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stop specific cell from recalculating?
I've created a new product as an Automation Addin. It's function is
similar to smart tags as it allows specific functions to push or pull data between Excel and remote servers via Web Services. When a customer ID changes in a cell, the cell next to it may recalculate, the formula will invoke the service and pull in a customer name or other data. The problem is that if the user inserts/deletes columns or rows the entire sheet is recalculated, and every cell with those formulas will trigger a web service call. I have a couple complex formulas that stop this from happening, but I won't expect users to put these formulas in every worksheet that uses the functions. So I want to allow the user to stop the firing of the specific cells with the formulas until they want them to calculate. If they change a customer ID they'll probably want the event to fire, but not if the entire worksheet does a shift. Ideally we could use an Excel function that says: =IF($Z$1,OurFunction(foo),NoOperation) The user could set Z1 to true as they wish to allow functions to execute or not. Unfortunately Excel returns a True or False value if the result of IF() is not specified. I'm posting to this forum because I'm hoping there is a formula to do this. If not then I'll code the AddIn to check for some conditions in the worksheet, and if they're not met, just don't invoke the remote call. Even then however, I still need to deal with making sure that the current cell value doesn't change - or passing the current value out and then returning the same value if we're doing a No-Op. I can solve the problems but I want the solution to be elegant. Any ideas before I start hacking away? Thanks!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stop specific cell from recalculating?
Rather than stopping certain cells from recalculating, here's a couple little
macros that will only recalculate specific cells when Recalc is set to Manual...........perhaps that will do as you wish. Sub ConditionalRecalcOLD() Dim myrange As Range Range("myrange").Calculate End Sub Sub ConditionalRecalc() With Selection ..Calculate End With End Sub Vaya con Dios, Chuck, CABGx3 "Tony Gravagno" wrote: I've created a new product as an Automation Addin. It's function is similar to smart tags as it allows specific functions to push or pull data between Excel and remote servers via Web Services. When a customer ID changes in a cell, the cell next to it may recalculate, the formula will invoke the service and pull in a customer name or other data. The problem is that if the user inserts/deletes columns or rows the entire sheet is recalculated, and every cell with those formulas will trigger a web service call. I have a couple complex formulas that stop this from happening, but I won't expect users to put these formulas in every worksheet that uses the functions. So I want to allow the user to stop the firing of the specific cells with the formulas until they want them to calculate. If they change a customer ID they'll probably want the event to fire, but not if the entire worksheet does a shift. Ideally we could use an Excel function that says: =IF($Z$1,OurFunction(foo),NoOperation) The user could set Z1 to true as they wish to allow functions to execute or not. Unfortunately Excel returns a True or False value if the result of IF() is not specified. I'm posting to this forum because I'm hoping there is a formula to do this. If not then I'll code the AddIn to check for some conditions in the worksheet, and if they're not met, just don't invoke the remote call. Even then however, I still need to deal with making sure that the current cell value doesn't change - or passing the current value out and then returning the same value if we're doing a No-Op. I can solve the problems but I want the solution to be elegant. Any ideas before I start hacking away? Thanks!!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stop specific cell from recalculating?
Sorry Tony..........for some reason I didn't get all of your post the first
time, therefore my response was somewhat inappropriate. Just ignore, and I'l go take the rest of my medications. Vaya con Dios, Chuck, CABGx3 "CLR" wrote: Rather than stopping certain cells from recalculating, here's a couple little macros that will only recalculate specific cells when Recalc is set to Manual...........perhaps that will do as you wish. Sub ConditionalRecalcOLD() Dim myrange As Range Range("myrange").Calculate End Sub Sub ConditionalRecalc() With Selection .Calculate End With End Sub Vaya con Dios, Chuck, CABGx3 "Tony Gravagno" wrote: I've created a new product as an Automation Addin. It's function is similar to smart tags as it allows specific functions to push or pull data between Excel and remote servers via Web Services. When a customer ID changes in a cell, the cell next to it may recalculate, the formula will invoke the service and pull in a customer name or other data. The problem is that if the user inserts/deletes columns or rows the entire sheet is recalculated, and every cell with those formulas will trigger a web service call. I have a couple complex formulas that stop this from happening, but I won't expect users to put these formulas in every worksheet that uses the functions. So I want to allow the user to stop the firing of the specific cells with the formulas until they want them to calculate. If they change a customer ID they'll probably want the event to fire, but not if the entire worksheet does a shift. Ideally we could use an Excel function that says: =IF($Z$1,OurFunction(foo),NoOperation) The user could set Z1 to true as they wish to allow functions to execute or not. Unfortunately Excel returns a True or False value if the result of IF() is not specified. I'm posting to this forum because I'm hoping there is a formula to do this. If not then I'll code the AddIn to check for some conditions in the worksheet, and if they're not met, just don't invoke the remote call. Even then however, I still need to deal with making sure that the current cell value doesn't change - or passing the current value out and then returning the same value if we're doing a No-Op. I can solve the problems but I want the solution to be elegant. Any ideas before I start hacking away? Thanks!!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Stop specific cell from recalculating?
Better living through chemistry! <g
-- Biff Microsoft Excel MVP "CLR" wrote in message ... Sorry Tony..........for some reason I didn't get all of your post the first time, therefore my response was somewhat inappropriate. Just ignore, and I'l go take the rest of my medications. Vaya con Dios, Chuck, CABGx3 "CLR" wrote: Rather than stopping certain cells from recalculating, here's a couple little macros that will only recalculate specific cells when Recalc is set to Manual...........perhaps that will do as you wish. Sub ConditionalRecalcOLD() Dim myrange As Range Range("myrange").Calculate End Sub Sub ConditionalRecalc() With Selection .Calculate End With End Sub Vaya con Dios, Chuck, CABGx3 "Tony Gravagno" wrote: I've created a new product as an Automation Addin. It's function is similar to smart tags as it allows specific functions to push or pull data between Excel and remote servers via Web Services. When a customer ID changes in a cell, the cell next to it may recalculate, the formula will invoke the service and pull in a customer name or other data. The problem is that if the user inserts/deletes columns or rows the entire sheet is recalculated, and every cell with those formulas will trigger a web service call. I have a couple complex formulas that stop this from happening, but I won't expect users to put these formulas in every worksheet that uses the functions. So I want to allow the user to stop the firing of the specific cells with the formulas until they want them to calculate. If they change a customer ID they'll probably want the event to fire, but not if the entire worksheet does a shift. Ideally we could use an Excel function that says: =IF($Z$1,OurFunction(foo),NoOperation) The user could set Z1 to true as they wish to allow functions to execute or not. Unfortunately Excel returns a True or False value if the result of IF() is not specified. I'm posting to this forum because I'm hoping there is a formula to do this. If not then I'll code the AddIn to check for some conditions in the worksheet, and if they're not met, just don't invoke the remote call. Even then however, I still need to deal with making sure that the current cell value doesn't change - or passing the current value out and then returning the same value if we're doing a No-Op. I can solve the problems but I want the solution to be elegant. Any ideas before I start hacking away? Thanks!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link from a specific Cell in Excel to a specific para. in Word | Excel Worksheet Functions | |||
please help! Linking a picture to a cell and recalculating automatically | Excel Discussion (Misc queries) | |||
Stop Recalculating? | Excel Discussion (Misc queries) | |||
cursor tracks / highlights recalculating cell? | Excel Discussion (Misc queries) | |||
cell/range with formula not recalculating | Excel Worksheet Functions |