VLOOKUP([cell reference from this spreadsheet tab]; [range in another workbook]; [column offset]; [sort?])
and I have learned that it is unwise to refer directly to “formula” cells in a formula if the source cell’s formula refers to an external spreadsheet, because when a spreadsheet opens and updates links, one cannot be certain that the links will be processed before the local formulae.
The problem I am having with Functions seems to be similar…
I have been having minor problems with my spreadsheet structures when I move them between drives, so if I have to move my documents from the C: drive to the D: drive, I have search for things like this:
and fix them manually.
So I am trying to improve things by hard-coding the paths in Basic with Functions that build the paths:
PRIVATE FUNCTION BUILDPATH() : BUILDPATH = CHAR(39) & CONST_PREFIX & CONST_DRIVE & CONST_PATH & CONST_FOLDER & CONST_ODSFILENAME & CHAR(39)
And then I do it, and it all works beautifully, and I sleep so nicely! And then I wake up in the morning, turn everything on, open the spreadsheet and I’ve got #NAME? all over the place, and all my
VLOOKUP([cell reference];INDIRECT(BUILDPATH()&"#$TabName.[range] have changed to
…and then I do a case-sensitive search and replace on the catatonic functions and search for lowercase and replace with upper case, and sometimes it works and sometimes it doesn’t work straight away, and then I have to do the search and replace again and again and again and eventually it works, drives me blinking bananas.
Please will someone help and thank you.
P.S. I have a small breakthrough with my dilemma. The case-sensitive search/replace mentioned above for the “catatonic functions” under discussion (and this is as much a note to self for future reference), only (?) works if you check the “diacritic sensitive” box. Doesn’t solve my “catatonic functions” problem though. Rubbing my hands together waiting for your genii to absolutely blossom for me. Thanks.