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!

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

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

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