Holiday Dates=Sorting Issue

[Data123]

I would like to sort a column and expand it to all the other columns, but I have a column with holidays (dates) listed. Several of my formulas will exclude those dates purposely, by listing the cell numbers by location. When I try to sort a column and expand the sort it moves the holiday dates all over and scrambling them to many cells. Is there a way hide the holiday dates column away from the spreadsheet, but still exclude them in necessary formulas?

 

[Data123]

so to be clear i have spreadsheet "a" that is my original sheet with the column of holiday dates shown in the original post above. i need to move the holiday dates to a separate sheet (lets call it sheet "b") and name it "holiday" in the name box? i also need to delete the holiday dates from my original spreadsheet (sheet a), correct?

so in my original sheet "a" i have columns that use formulas. in those formulas they list the cells of the holiday dates to exclude them from being used when calculating data. here is part of the formula for the holiday dates; (WORKDAY(TODAY(), -10,$N$2:$N$13)). the "$N$2:$N$13" represents the cells where the original holiday dates reside. so my question is if i move the holiday dates to another sheet, what do i replace, "$N$2:$N$13" with and how will it know to look at sheet "b"?

 

[kvsrinivasamurthy]

Say holiday dates are shifted to sheet "abcd" in B2:B13.
In the formula $N$2:$N$13 is to be replaced by 'abcd'!$B$2:$B$13

 

[Alex Blakenburg]

If you "cut" and paste the holiday cells to a new sheet you shouldn't have to change anything, your formulas should update.

 

[Data123]

If you "cut" and paste the holiday cells to a new sheet you shouldn't have to change anything, your formulas should update.

Thanks, yes that is what I did (cut and paste) and it did not make the change in the formula.

 

[Alex Blakenburg]

Thanks, yes that is what I did (cut and paste) and it did not make the change in the formula.

If you formula is
- (WORKDAY(TODAY(), -10,$N$2:$N$13))
did you select that exact range $N$2:$N$13, then cut it and move it to another sheet ?
If you are referring to that range in more than one formula are they looking at exactly the same range ?

If it still doesn't work show us the cell with your formula in it and in the same screenshot include the formula box at the top so we can see the formula.
Do this before and after you have cut and paste the formula moving it to another sheet.

 

[Data123]

Thanks, does anyone know if the order of dates for holidays makes any difference? If so is the order simply the earliest date and year prior to later? Also, what format should the dates be in 01/01/2024 or 1/1/2024?

 

[Alex Blakenburg]

does anyone know
• if the order of dates for holidays makes any difference?
No - I don't believe so.
• If so is the order simply the earliest date and year prior to later?
N/A based on the above
• Also, what format should the dates be in 01/01/2024 or 1/1/2024?
The date format shouldn't matter as long as all the dates are being recognised in Excel as Dates (and not Text)

Note: quickest way to check they are not text, highlight the cells with the dates, Ctrl+Shift+~, they should all change to be numbers. Ctrl+Z (undo) to restore the date format. (Anything that doesn't change is being seen as text)

 

[Data123]

does anyone know
• if the order of dates for holidays makes any difference?
No - I don't believe so.
• If so is the order simply the earliest date and year prior to later?
N/A based on the above
• Also, what format should the dates be in 01/01/2024 or 1/1/2024?
The date format shouldn't matter as long as all the dates are being recognised in Excel as Dates (and not Text)

Note: quickest way to check they are not text, highlight the cells with the dates, Ctrl+Shift+~, they should all change to be numbers. Ctrl+Z (undo) to restore the date format. (Anything that doesn't change is being seen as text)

thanks alex! glad the date format or order does not matter. also, the dates became numbers great to know this verification strategy! may i ask can you look at this post in case you know the answer please? Sort Column But Exclude Rows On Bottom

 

[Alex Blakenburg]

You're welcome. Glad we could help.