Holiday Dates Moving On Their Own

[Data123]

See attached image. I have holidays listed in cells "O2:O9" in column "O". So when I look at my Workday formula in column "N" , the first cell after the header shows the location as "O2:O9", which is perfect and it stays at this location.

However, if I look at the formula from the second cell in column "N" the holiday location moved to "O3:O10" and the third cell shows "O4:O11" and so on. Isn't the holiday location supposed to stay at the "O2:O9" location? When I correct cell two and three of column "N" to "O2:O9" they both revert to their incorrect holiday location.

 

[Asbestos_Jen]

You need to change your references to absolute in your first formula before you fill down. Replace O2:O9 with $O$2:$O$9. That way they won't increment when you fill or copy down.

Another option would be to make your holiday dates a named range and use that name in your formula.

 

[Data123]

You need to change your references to absolute in your first formula before you fill down. Replace O2:O9 with $O$2:$O$9. That way they won't increment when you fill or copy down.

Another option would be to make your holiday dates a named range and use that name in your formula.

thank you, that worked!!!! may i ask one of the columns shows "#BUSY!" in the cells now. Is this normal?

 

[Asbestos_Jen]

The #BUSY error is new to Office 365, so I've never seen it (I'm on 2007 still). From the little I've read on it, it means a calculation (or series of them) is taking a long time. How to correct a #BUSY! error - Microsoft Support

How big is your spreadsheet?

 

[Data123]

850 rows and so far it's 12 columns wide, but i am not done and wanted to add several tabs (new sheets +). all i can say is i left excel alone for 45 min and no change then i closed the app and reloaded and 85% of the #BUSY! statements went away. do you pull stock data? excel 2007 sounds great if you have no throttle issues. may i ask are there any other common items in formulas i need to lock in with $$ signs?

 

[Asbestos_Jen]

I haven't used Office 365, but from what I've seen on this forum, it is far more powerful, with many advanced functions. I don't pull stock data. If your sheet is referencing external data or has many volatile functions, that will slow it down.
Generally, if the formulas need to always reference a specific row, put the $ in front of the row number. If they need to always reference a specific column, put the $ in front of the column letter. If a specific cell or range of cells (as with your holiday list), then both letter and number get $.

 

[Data123]

Thank you and I very much appreciated your help!

 

[Asbestos_Jen]

Glad to have helped. Thanks for the feedback.