function question

S

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.
 
L

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.
 
S

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.


Luke M said:
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!*


SteveDB1 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top