# Dynamic Named Range based on values in multiple columns



## spence524 (Jan 5, 2023)

Is there way to create a dynamic named range based on cell values from other columns?  The cells in the named range may not be contiguous.
For example, I have a list of holidays or other observed days.  I want a dynamic named range of the Day column to reference only those marked as Type: Holiday, and Mark on Calendar = TRUE.
If I remember, there was a combination of OFFSET and perhaps INDEX/MATCH.  I've been searching other forums and pages and have not been able to find the solution.


----------



## Jeffrey Mahoney (Jan 5, 2023)

What are you trying to return?  The date or the Holiday description?

I would create a helper column that tests the Type and Mark on Calendar:
=IF(AND(B2="Holiday",C2=True),D2,"")
This puts a date in column E only if Holiday and Mark are true.


----------



## spence524 (Jan 5, 2023)

Jeffrey Mahoney said:


> What are you trying to return?  The date or the Holiday description?
> 
> I would create a helper column that tests the Type and Mark on Calendar:
> =IF(AND(B2="Holiday",C2=True),D2,"")
> This puts a date in column E only if Holiday and Mark are true.


The Date.
So the named range would end up having {1/1/23;1/16/23;2/20/23......} and so on.  
Ahh, a helper column sounds like a great idea to help with the multiple conditions.  Great idea!


----------



## Jeffrey Mahoney (Jan 5, 2023)

Then you can use the FILTER function to return the dates from the new helper column


----------



## Jeffrey Mahoney (Jan 5, 2023)

You should update your profile so we can see what version of MS Office and Operating system being used.


----------



## spence524 (Jan 5, 2023)

Jeffrey Mahoney said:


> Then you can use the FILTER function to return the dates from the new helper column


I can use the FILTER function, but other users may not be able to.


----------



## Jeffrey Mahoney (Jan 5, 2023)

Provide data instead of a picture.  I can create a function that doesn't use FILTER


----------



## spence524 (Jan 5, 2023)

Jeffrey Mahoney said:


> Provide data instead of a picture.  I can create a function that doesn't use FILTER


----------



## spence524 (Jan 5, 2023)

I would like to be able to take the dates that are in *bold* and have that be a dynamic named range based on the helper column F.  Currently, I have manually named the range for clarity.  Thanks for looking at this!

Book1ABCDEF1DayDescriptionTypeMark on Calendar?Dateh_ActiveHoliday2New Year's Day1st Day in JanuaryHolidayTRUE1/1/2023TRUE3Martin Luther King Jr.'s Birthday (Observed)3rd Monday in JanuaryHolidayTRUE1/16/2023TRUE4Presidents Day3rd Monday in FebruaryHoliday2/20/2023FALSE5Good FridayFriday Before EasterHolidayTRUE4/7/2023TRUE6EasterVolatileHolidayTRUE4/9/2023TRUE7Memorial DayLast Monday in MayHolidayTRUE5/29/2023TRUE8Flag Day14th Day in JuneHoliday6/14/2023FALSE9Independence Day4th Day in JulyHolidayTRUE7/4/2023TRUE10Labor Day1st Monday in SeptemberHolidayTRUE9/4/2023TRUE11Columbus Day2nd Monday in OctoberHolidayTRUE10/9/2023TRUE12Thanksgiving Day4th Thursday in NovemberHolidayTRUE11/23/2023TRUE13Day After ThanksgivingDay After ThanksgivingHoliday11/24/2023FALSE14Christmas EveDay before ChristmasHolidayTRUE12/24/2023TRUE15Christmas Day25th Day in DecemberHolidayTRUE12/25/2023TRUESheet1Cell FormulasRangeFormulaE2E2=DATE(YEAR(NOW()),1,1)F2:F15F2=AND([@Type]="Holiday",[@[Mark on Calendar?]]=TRUE)E3E3=DATE(YEAR(NOW()),1,1)+IF(2<WEEKDAY(DATE(YEAR(NOW()),1,1)),7-WEEKDAY(DATE(YEAR(NOW()),1,1))+2,2-WEEKDAY(DATE(YEAR(NOW()),1,1)))+((3-1)*7)E4E4=DATE(YEAR(NOW()),2,1)+IF(2<WEEKDAY(DATE(YEAR(NOW()),2,1)),7-WEEKDAY(DATE(YEAR(NOW()),2,1))+2,2-WEEKDAY(DATE(YEAR(NOW()),2,1)))+((3-1)*7)E5E5=(DOLLAR(("4/"&YEAR(NOW()))/7+MOD(19*MOD(YEAR(NOW()),19)-7,30)*14%,)*7-6)-2E6E6=DOLLAR(("4/"&YEAR(NOW()))/7+MOD(19*MOD(YEAR(NOW()),19)-7,30)*14%,)*7-6E7E7=DATE(YEAR(NOW()),6,1)+IF(2<WEEKDAY(DATE(YEAR(NOW()),6,1)),7-WEEKDAY(DATE(YEAR(NOW()),6,1))+2,2-WEEKDAY(DATE(YEAR(NOW()),6,1)))+((1-1)*7)-7E8E8=DATE(YEAR(NOW()),6,14)E9E9=DATE(YEAR(NOW()),7,4)E10E10=DATE(YEAR(NOW()),9,1)+IF(2<WEEKDAY(DATE(YEAR(NOW()),9,1)),7-WEEKDAY(DATE(YEAR(NOW()),9,1))+2,2-WEEKDAY(DATE(YEAR(NOW()),9,1)))+((1-1)*7)E11E11=DATE(YEAR(NOW()),10,1)+IF(2<WEEKDAY(DATE(YEAR(NOW()),10,1)),7-WEEKDAY(DATE(YEAR(NOW()),10,1))+2,2-WEEKDAY(DATE(YEAR(NOW()),10,1)))+((2-1)*7)E12E12=DATE(YEAR(NOW()),11,1)+IF(5<WEEKDAY(DATE(YEAR(NOW()),11,1)),7-WEEKDAY(DATE(YEAR(NOW()),11,1))+5,5-WEEKDAY(DATE(YEAR(NOW()),11,1)))+((4-1)*7)E13E13=DATE(YEAR(NOW()),11,1)+IF(5<WEEKDAY(DATE(YEAR(NOW()),11,1)),7-WEEKDAY(DATE(YEAR(NOW()),11,1))+5,5-WEEKDAY(DATE(YEAR(NOW()),11,1)))+((4-1)*7)+1E14E14=DATE(YEAR(NOW()),12,24)E15E15=DATE(YEAR(NOW()),12,25)Cells with Data ValidationCellAllowCriteriaD2:D15ListTrue, False


----------



## shinigamilight (Jan 6, 2023)

G2:   =IFERROR(INDEX(Table5[Date],AGGREGATE(15,6,(ROW(Table5[Mark on Calendar?])-1)/(--(Table5[Type]="Holiday")*--(Table5[Mark on Calendar?]=TRUE)),ROWS($G$2:G2))),"")


Maybe something like this?


----------



## spence524 (Jan 5, 2023)

Is there way to create a dynamic named range based on cell values from other columns?  The cells in the named range may not be contiguous.
For example, I have a list of holidays or other observed days.  I want a dynamic named range of the Day column to reference only those marked as Type: Holiday, and Mark on Calendar = TRUE.
If I remember, there was a combination of OFFSET and perhaps INDEX/MATCH.  I've been searching other forums and pages and have not been able to find the solution.


----------



## spence524 (Jan 7, 2023)

Thank you for providing this.  Given other needs in my application, it made the most sense to me to use spill formulas (I was trying to avoid it).  I used a FILTER with multiple conditions:

FILTER(tbl_Holidays[Date],(tbl_Holidays[Type]="Holiday")*(tbl_Holidays[Mark on Calendar?]=TRUE))


----------

