P
Paula
I am looking for a visual basic macro that would do the following:
Convert an indirect function into a direct link. I have a variety of
indirect functions in a file that link to external files, and as the
indirect function is volatile, these fields "#ref" out when the files
are closed. Rather than redo all of these to be direct links, I would
like a macro that goes through and converts the indirects to direct
links. Since all the information is on the sheet and in the formula,
it seems like it should be straightforward, but is beyong my simple
VBA programming abilities.
For greater clarity, I am using the following types of indirect
functions:
SUM(INDIRECT("'"&E$8&E$9&"'"&"!"&E$10&$A18&":"&E$11&$A18))
Where E$8 is filename (e.g. "File")
E$9 is the sheet name (e.g. "Sheet1")
E$10 is the beginning column (e.g. "D")
E$11 is the ending column (e.g. "F")
$A18 is the row (e.g. 10)
So the macro would convert the above macro to:
SUM('[File]Sheet1'!$D$10:$F$10)
I would like this macro to be made generic so it applies to different
indirect functions (e.g. some are sums, some are single links), and to
be set up so I can run it for all indirects that are currently
selected (e.g. I highlight a range of cells, hit the macro shortcut
key, and all indirects in those cells are converted to direct links,
and cells with other formulas are not affected.
I put this in as a google answers post too, if anyone wants the money!
http://answers.google.com/answers/threadview?id=711588
Thanks in advance
PC
Convert an indirect function into a direct link. I have a variety of
indirect functions in a file that link to external files, and as the
indirect function is volatile, these fields "#ref" out when the files
are closed. Rather than redo all of these to be direct links, I would
like a macro that goes through and converts the indirects to direct
links. Since all the information is on the sheet and in the formula,
it seems like it should be straightforward, but is beyong my simple
VBA programming abilities.
For greater clarity, I am using the following types of indirect
functions:
SUM(INDIRECT("'"&E$8&E$9&"'"&"!"&E$10&$A18&":"&E$11&$A18))
Where E$8 is filename (e.g. "File")
E$9 is the sheet name (e.g. "Sheet1")
E$10 is the beginning column (e.g. "D")
E$11 is the ending column (e.g. "F")
$A18 is the row (e.g. 10)
So the macro would convert the above macro to:
SUM('[File]Sheet1'!$D$10:$F$10)
I would like this macro to be made generic so it applies to different
indirect functions (e.g. some are sums, some are single links), and to
be set up so I can run it for all indirects that are currently
selected (e.g. I highlight a range of cells, hit the macro shortcut
key, and all indirects in those cells are converted to direct links,
and cells with other formulas are not affected.
I put this in as a google answers post too, if anyone wants the money!
http://answers.google.com/answers/threadview?id=711588
Thanks in advance
PC