User Profile
Daktal
Copper Contributor
Joined Sep 07, 2021
User Widgets
Recent Discussions
Re: Prevent formula from changing range when cut and paste data
HansVogelaar 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.9.3KViews0likes0CommentsPrevent 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 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?Solved10KViews0likes3Comments
Recent Blog Articles
No content to show