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?

 

[dermie_72]

are the holiday dates hard coded or values of formula?

 

[Data123]

are the holiday dates hard coded or values of formula?

I am too new to understand this sorry. I just created a column named "holidays" and then added the dates shown in the screenshot above. Then in other columns I have the holiday cell numbers shown in the formula to exclude them ie., $M3$M12

 

[dermie_72]

As in when you click in the cell with the date in it, do you see the date, or is there a formula in THAT cell.
IE When you clock on the holiday 1/1/2024, do you see 1/1/2024 or a formula

 

[Data123]

As in when you click in the cell with the date in it, do you see the date, or is there a formula in THAT cell.
IE When you clock on the holiday 1/1/2024, do you see 1/1/2024 or a formula

I understand now, thanks for the explanation. I only see a date when I click on 1/1/24 NO formula at all.

 

[dermie_72]

Can you please share a copy of you data in an XL2BB file so we can have a play and identify the cause

 

[kvsrinivasamurthy]

Keep the Holidays list in a Separate Sheet. Name the sheet as "Holidays'. Don't put any data in that.

 

[Data123]

Keep the Holidays list in a Separate Sheet. Name the sheet as "Holidays'. Don't put any data in that.

thanks! when you say don't put any data in it what do you mean?

 

[Data123]

I forgot to ask if I move the "holiday" dates to another sheet and place those dates in A1-A13 the original sheet's formulas already have data in those same cells. If by naming the "holiday" sheet do I then replace the old dates with just the name Holiday in the formula? If so any special characters like $holiday?

 

[kvsrinivasamurthy]

Is it possible to upload a sample file, showing the problem. So that it will be easy to find solutions.