Forum Discussion

Daktal's avatar
Daktal
Copper Contributor
Sep 08, 2021
Solved

Prevent formula from changing range when cut and paste data

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 i...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Sep 08, 2021

    Daktal 

    You might use the INDIRECT function:

     

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

     

    or

     

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 08, 2021

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.

 

Resources