Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ---------------------------------------------- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ---------------------------------------------- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ---------------------------------------------- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hyper links in pull down menus | Excel Discussion (Misc queries) | |||
Hyperlinks in pull downs . . . | Excel Worksheet Functions | |||
How can I pull data from multiple user workbooks into one? | Excel Discussion (Misc queries) | |||
Referencing multiple criteria to pull data | Excel Discussion (Misc queries) | |||
Why does AutoFilter not show all the data in the pull down? | Excel Discussion (Misc queries) |