for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

run free May 2022 10:23pm, 8 May 2022 4,970 posts	Quote Pin run free +1 for multiplying by one
larkim May 2022 10:42am, 13 May 2022 17,868 posts	Quote Pin larkim I wanted to create a dropdown box listing sheets in a workbook. I followed howtoexcel.org which defines a name ("sheetnames") and references what looks like VBA-ish in a formula:- =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") Worked fine at first but it doesn't refresh or update, so would like a way to force a refresh. Also, I tried CTRL-SHFT-ALT-F9 which google says "Shift + Ctrl + Alt + F9 Rebuilds the dependency tree and does a full recalculation." just to try something. Now the output of formulas which refer to the named range "sheetnames" returns an error value I've not seen before "#BLOCKED!" This is within a macro-enabled workbook. Anyone know what I'm encountering with the #BLOCKED! error message?
GregP May 2022 10:52am, 13 May 2022 135,298 posts	Quote Pin GregP Blimey. I shall watch this with interest but have no wisdom to impart. Not at all.
larkim May 2022 10:57am, 13 May 2022 17,869 posts	Quote Pin larkim Righty, oh. I've unpicked the "blocked" message, that was a trust centre issue; looks like perhaps the complex f9 button pressing may have reset the trust centre options to block macros and older excel macro formats. Now all I need to do is find a way for that formula to refresh the listing.
um May 2022 11:24am, 13 May 2022 6,060 posts	Quote Pin um Never heard of this before, but another web site suggests (well, says) myonlinetraininghub.com =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW()) We do this because NOW, which returns the current time, triggers a recalculation of the defined name. The T function returns blank when the value returned isn’t text. In other words, T hides the time returned by NOW. The only reason we’re appending the NOW function is because it’s a volatile function that triggers a recalculation of the defined name which is required to update the list of sheet names.
larkim May 2022 11:29am, 13 May 2022 17,870 posts	Quote Pin larkim Fab, will give that a try.
larkim May 2022 11:34am, 13 May 2022 17,871 posts	Quote Pin larkim That works great, thanks for that spot. Doesn't recalc when a new sheet is added, bizarrely, but when a sheet is deleted or renamed it does trigger the recalc which is exactly what I needed!
quimby May 2022 7:12pm, 17 May 2022 5,609 posts	Quote Pin quimby I attended a short Excel training session at work billed as "Getting Data into Excel". I thought it would be tips on importing data from other sources. Turned out it was "how to type data into Excel". It included a description of how to identify the Ctrl key on your keyboard. 🙄 I didn't leave early because I was fascinated by the trainer's pronunciation of "coll-you-ummms".
GregP May 2022 7:51pm, 17 May 2022 135,404 posts	Quote Pin GregP That’s a brilliant post Q. Just splendid. I used to have an (Irish) cow-orker who said ‘Coll-yum’ which I thought was plenty strange enough.
quimby May 2022 7:53pm, 17 May 2022 5,611 posts	Quote Pin quimby She was Irish, yes.