function question

[SteveDB1]

morning all.
I have a template workbook (xltx) that I've configured with all of my
worksheet functions.
I then have a macro that I can import/copy specific worksheets over from my
template to an active workbook.
Presently, when I activate the macro to copy a worksheet over, it retains
the name of the template workbook in my worksheet functions.
I.e.,
what starts off as
=subtotal(109,ShtNm!A1:A100)
becomes
=subtotal(109,'C:\Documents and Settings\username\Application
Data\Microsoft\Templates\[FileNm.xltx]ShtNm'!A1:A100)

What I'd like to have is that the worksheet function just remain
=subtotal(109,ShtNm!A1:A100)

How can I accomplish that?
Or can I?

Your helps are appreciated.

 

[Luke M]

Easiest way may be to rewrite your formulas to indirect references.
=subtotal(109,INDIRECT("ShtNm!A1:A100"))

This would lock in the name of your sheets so that even if it moves to a
different workbook, the formula will still try to find a sheet by the name
you designate (note that this could create an error if sheet name is not
found). Downside is that the array will not shift like XL normally does if
you were to copy it vertically/horizontally.

 

[SteveDB1]

hi Luke,
I tried as you said..... it doesn't work either.

=SUBTOTAL(103,INDIRECT('C:\Documents and Settings\username\Application
Data\Microsoft\Templates\[FileNm.xltx]ShtNm'!A1:A100))

Thank you for trying.

Easiest way may be to rewrite your formulas to indirect references.
=subtotal(109,INDIRECT("ShtNm!A1:A100"))

This would lock in the name of your sheets so that even if it moves to a
different workbook, the formula will still try to find a sheet by the name
you designate (note that this could create an error if sheet name is not
found). Downside is that the array will not shift like XL normally does if
you were to copy it vertically/horizontally.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

morning all.
I have a template workbook (xltx) that I've configured with all of my
worksheet functions.
I then have a macro that I can import/copy specific worksheets over from my
template to an active workbook.
Presently, when I activate the macro to copy a worksheet over, it retains
the name of the template workbook in my worksheet functions.
I.e.,
what starts off as
=subtotal(109,ShtNm!A1:A100)
becomes
=subtotal(109,'C:\Documents and Settings\username\Application
Data\Microsoft\Templates\[FileNm.xltx]ShtNm'!A1:A100)

What I'd like to have is that the worksheet function just remain
=subtotal(109,ShtNm!A1:A100)

How can I accomplish that?
Or can I?

Your helps are appreciated.