Moving and formatting

Hi all, I have a question that if anyone could help with I would be most appreciative.

I work for a hostel and we use Excel to allocate our rooms and show availability. In order to make things easier I've created an excel file that counts available rooms automatically using the COUNTIF function, however I've come across a problem. Regularly we cut and paste reservations from one room to another, the problem is that when I do this it changes the reference of the countif formula so it returns inaccurate results. To clarify, if I have the formula:

=COUNTIF(B221:B225,0)

and I cut the reservation from the room at B225 then paste into another room in B230, it changes the formular to:

=COUNTIF(B221:B230,0)

which obviously then covers a greater spread and returns incorrect results.

My question is, is it possible to write a formula that will keep constant ranges regardless of cutting and pasting. Additionally, is there a way to cut only the value of a selection of cells and not the formatting? I have conditional formatting set up to highlight available rooms, but once I move a reservation it brings the formatting with it so the empty room is no longer correctly formatted.

I hope all this makes sense, thanks for any help you can offer.

See if links help


Absolute References

Paste Special

VBA Noob