How can I use non-absolute workbook/range references in formulas
 

I have some complicated macros which help create quotes for our sales
department. I am using a "configurator" workbook which contains a few
template sheets and over 50 ranges which point to various
variables/templates. In the process of creating the quotes I end up
opening/creating a new workbook and copying the template ranges/sheets
to the new workbook (through the macro). This new "Quotes" workbook
will contain a coversheet with many variables containing named ranges
which subsequent sheets will need to reference with "=NAMED_RANGE" or
something like that.

Right now excel is annoying me because when it copies references of
named ranges from one workbook to another, the cell tries to refer back
to the "configuration" workbook (although it doesn't say so). I want to
be able to use something like "=ThisWorkbook!NAMED_RANGE" which would
solve all my problems but it looks like MS isn't going to make it that
easy for me (I can't find any info on a special reference like
ThisWorkbook for formulas).

The only workaround I could think of would be to create a macro to scan
through all the cells with references to named ranges in the new "Quote"
workbook and insert the new workbook name in front of them (i.e.
=quote1.xls!NAMED_RANGE). This would take forever and then I would
have problems if the user renamed the workbook.

Anyone have any suggestions? Please I'm dying here. :mad:


--
rnabinger
------------------------------------------------------------------------
rnabinger's Profile: http://www.excelforum.com/member.php...o&userid=36537
View this thread: http://www.excelforum.com/showthread...hreadid=562927