Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function question
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" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
function question
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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Question | Excel Worksheet Functions | |||
Question on which function to use | Excel Worksheet Functions | |||
IF function question | Excel Worksheet Functions | |||
Function Question | Excel Discussion (Misc queries) | |||
function question | Excel Worksheet Functions |