CELL("filename") acting Volatile? (in assoc. with PULL)
Hello,
I was using Harlan Grove's PULL function for the first time, and found that it had started behaving in a Volatile manner, when it shouldn't have. I was able to hunt down the problem to my formula that builds the reference that PULL uses. As a step in building the directory of the external workbook, I used "=LEFT(CELL ("filename",A1),FIND("[",CELL("filename",A1))-1)" to return the directory of the current workbook. This formula is the problem. When I remove it, PULL no longer acts Volatile. But it was my understanding that CELL("filename") was not volatile. (Sources: <http://www.decisionmodels.com/calcsecretsi.htm and <http://support.microsoft.com/kb/211370/EN-US/) Does anyone know why things became volatile in my workbook? (BTW, I wrote a tiny UDF to return the directory of the current workbook, instead of using the CELL function, and the workbook works fine now.) Thanks, Shawn -- ---------------------------------------------- Posted with NewsLeecher v3.0 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
CELL("filename") acting Volatile? (in assoc. with PULL)
(Sorry, my last post wasn't formatted properly... Here it is again.)
Hello, I was using Harlan Grove's PULL function for the first time, and found that it had started behaving in a Volatile manner, when it shouldn't have. I was able to hunt down the problem to my formula that builds the reference that PULL uses. As a step in building the directory of the external workbook, I used "=LEFT(CELL("filename",A1),FIND("[",CELL ("filename",A1))-1)" to return the directory of the current workbook. This formula is the problem. When I remove it, PULL no longer acts Volatile. But it was my understanding that CELL("filename") was not volatile. (Sources: http://www.decisionmodels.com/calcsecretsi.htm and http://support.microsoft.com/kb/211370/EN-US/) Does anyone know why things became volatile in my workbook? (BTW, I wrote a tiny UDF to return the directory of the current workbook, instead of using the CELL function, and the workbook works fine now.) Thanks, Shawn -- ---------------------------------------------- Posted with NewsLeecher v3.0 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
CELL("filename") acting Volatile? (in assoc. with PULL)
But I read it on the internet! So it must be true! ;)
Thanks for the analysis, Harlan. PULL is very handy, thank you. Shawn -- ---------------------------------------------- Posted with NewsLeecher v3.0 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
CELL("filename") acting Volatile? (in assoc. with PULL)
Hi Harlan,
Just to confirm that Cell("filename") shows up as volatile when I test it too: guess I got lazy and believed the MSKB article. Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Harlan Grove" wrote in message ups.com... wrote... ... But it was my understanding that CELL("filename") was not volatile. (Sources: http://www.decisionmodels.com/calcsecretsi.htm and http://support.microsoft.com/kb/211370/EN-US/) ... Don't believe everything you read. Testing is always best to confirm. In this case, create a nonvolatile udf like Function nada() Static n As Long n = n + 1 MsgBox n End Function then enter 2 separate formulas, =CELL("Filename")&nada() and =nada() Now clear the cell containing the latter, and the former recalcs. Undo the clear. Now clear the cell containing the former, but the latter doesn't recalc. Sure looks like CELL("Filename") is volatile despite what Charles Williams's site may say. |
All times are GMT +1. The time now is 11:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com