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!