Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
global user-defined name (range) - or help with holidays
not sure if should post here or in the main excel group, but since i'm utilizing VBA i'll put it here first.
on sheet1.xlsm i have a user defined name range of dates named holidays used to calculate target dates (ex, mid-month target for febuary, typically the 15th, is actually the 12th this year due to weekend/holiday) on the vba code i am creating sheet2 that uses a formula that figures out the mid-month target, but the 'holiday' part is not working since the named range is on sheet1 but the formula is created on sheet2 - the formula in it's full glory - =WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(WO RKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY( ))-1),1))15),16),-1,holidays) the simple answer is to have the formula placed someplace in sheet1 where the named range exists, but with some other things going on, it is likely more cumbersome to make the adjustments to do this than to make the 'holidays' named range work in sheet2. so, how do i make the named range global? thank you in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
global user-defined name (range) - or help with holidays
Matthew,
I like to avoid using global scope names as much as possible for many reasons. If you want to ref local scope names from another sheet... =WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(WO RKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY( ))-1),1))15),16),-1,sheet1!holidays) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
global user-defined name (range) - or help with holidays
That worked! just had additional info since it's not just in a diff sheet but in a diff excel book altogether, but figured it out. Thanks for pointing me in the right direction!
On Wednesday, February 3, 2016 at 4:06:09 PM UTC-7, Matthew Dyer wrote: not sure if should post here or in the main excel group, but since i'm utilizing VBA i'll put it here first. on sheet1.xlsm i have a user defined name range of dates named holidays used to calculate target dates (ex, mid-month target for febuary, typically the 15th, is actually the 12th this year due to weekend/holiday) on the vba code i am creating sheet2 that uses a formula that figures out the mid-month target, but the 'holiday' part is not working since the named range is on sheet1 but the formula is created on sheet2 - the formula in it's full glory - =WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(WO RKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY( ))-1),1))15),16),-1,holidays) the simple answer is to have the formula placed someplace in sheet1 where the named range exists, but with some other things going on, it is likely more cumbersome to make the adjustments to do this than to make the 'holidays' named range work in sheet2. so, how do i make the named range global? thank you in advance! that |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
global user-defined name (range) - or help with holidays
That worked! just had additional info since it's not just in a diff
sheet but in a diff excel book altogether, but figured it out. Thanks for pointing me in the right direction! On Wednesday, February 3, 2016 at 4:06:09 PM UTC-7, Matthew Dyer wrote: not sure if should post here or in the main excel group, but since i'm utilizing VBA i'll put it here first. on sheet1.xlsm i have a user defined name range of dates named holidays used to calculate target dates (ex, mid-month target for febuary, typically the 15th, is actually the 12th this year due to weekend/holiday) on the vba code i am creating sheet2 that uses a formula that figures out the mid-month target, but the 'holiday' part is not working since the named range is on sheet1 but the formula is created on sheet2 - the formula in it's full glory - =WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(WO RKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY( ))-1),1))15),16),-1,holidays) the simple answer is to have the formula placed someplace in sheet1 where the named range exists, but with some other things going on, it is likely more cumbersome to make the adjustments to do this than to make the 'holidays' named range work in sheet2. so, how do i make the named range global? thank you in advance! that That's great! Also good you figured out the additional syntax for how to ref a sheet in another workbook. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Range | Excel Programming | |||
User-defined range for graph | Excel Discussion (Misc queries) | |||
User Defined Functions - local or global? | Excel Worksheet Functions | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming |