Prevent formula from changing range when cut and paste data

[Daktal]

I have a workbook on a shared drive that about 10 people access and need to change specific data. In the workbook I have a range of cells representing beds in rooms. 5 rooms, 20 beds each, arranged in a column with space between each group. Each cell represents one bed and either has a name or it doesn't. At the end of the range is a formula that checks how many cells have names and outputs a number giving a total number of occupied beds. It looks like this:

=SUM(--ISTEXT($C$63:$C$82))

The problem is, If a user cuts a name from one room and pastes it into a cell of another room, the formula wants to include all the cells from the point of origin to the new cell. I've since learned this is a feature and desired by many users. I am not one of them. My own knowledge of excel is sketchy and the rest of the people that need to use this workbook are not very computer friendly as it is. They will definitely cut and paste no matter how many emails I send out asking to copy and paste. Is there any way to lock that formula and make this workbook idiot proof? Or is there a different way to write that formula so it is only concerned with whether or not there is data in the cell and not the actual data itself?

[#2 · Accepted solution]

@Daktal 

You might use the INDIRECT function:

=SUM(--ISTEXT(INDIRECT("C63:C82")))

or

=COUNTA(INDIRECT("C63:C82"))

[#3]

@Hans Vogelaar 

Thank you! After some googling, I figured the INDIRECT tag was going to be the key, but I couldn't for the life of me get the syntax down properly. I just tried it for one formula and it worked the way I need it to. I'll try it on the rest and put it in production and see how it works. But, it looks like this is the answer. Thank you very much.

[#4]

@Daktal 

I am with your staff on this!  Of course they will want to use Ctrl/X, Ctrl/V to move a booking from one room to another.  The problem is not the users, it is the fragility of virtually all spreadsheet solutions.

In your case, I would place the entire dataset within a single named range 'beds'.  The data for any given room 'roomNum' is determined by the stride length in rows between rooms (25 say) and the count of beds per room (20).  The room data and occupancy level are then returned by

= OFFSET(Beds,25*(roomNum-1),0,20,1)

= COUNTA(OFFSET(Beds,25*(roomNum-1),0,20,1))

 It doesn't help right now, but something called Lambda functions are now on beta release that will allow you to define a function to return the occupancy for any given room, e.g.

= OCCUPIED("Room2")

= COUNTA(OCCUPIED("Room2"))

In this 'glammed-up' version, the room data and bed count could be returned by XLOOKUP from a pre-defined table that describes the data layout.