Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
locking a formula
i have to organise a list of passengers on a mini-bus.
Row 6 contains a list of those passengers, with the first column after the primary column containing a count function telling me what the total number of passengers there are in row 6.
Row 7 is blank and if a passenger is not going to be on the minibus i drag them down from row 6 into row 7. now here is my problem - when i drag them down, the operation also expands the count function to include row 7, therefore my total passenger count stays the same, whereas when i drag them down from row 6 to row 7 i want my row 6 count to decrease. In other words i need to lock the count function into row 6.
I've tried putting the $ sign in various places, but to to no avail.
help please!
Joe
Comments
-
Hmmm. hard to imagine what you are talking about. But maybe you could consider indenting your rows into a parent row and then dragging the person out of the indented row... you can use a count of the children to get your total for the minibus? =Count(children(PrimaryColumnname1))
Substitute the PrimaryColumnName1 with your primary column and the row number of the parent.
When items are dragged out of the parent task then it won't count it. You just have to make sure its unindented if you drag it right below the list of indented items.
-
Thanks Mike
I'll try that - was hoping to be able to be able to simply lock the function into row 6 - is there a way of uploading a smartsheet to this chat ?- i could create a couple of lines and upload it so you can see what i am on about!
Joe
-
For the last question, you can publish a view only or editable version of the sheet, See the Publish tab at the bottom of your Smartsheet sheet.
To the original point, to drag a value from one cell to another, hover over the mouse over the cell border until you see the white four direction arrow, and click-hold and drag the mouse to the new cell location. Is that what you mean by drag? My formulas do not change when I do that. Are you editing the formula at the time you do this?
Aside: This drag feature is not natural for me and sometimes does not do what I expect it to do. Likely it is just user error.
Again back to the original post, if you are an Admin (and I assume you are) then locking the row or column may prevent this (if we can figure out what "this" is) for other users but not yourself.
Lastly, to Mike's point, new users sometime struggle with Smartsheet's vertical orientation. It looks like a spreadsheet, but it is not quite the same as sheet in Google Sheets or Microsoft Excel. By having your mini-bus occupants in rows instead of in a single column can be done but may lose some of the benefits of Smartsheet, such as Mike's use of hierarchies.
I hope that helps.
Craig
-
If you wanted to make a copy of your sheet as it is now and share it as editable to this discussion, then we could go in and set it up for you or at least see what it is you are trying to accomplish.
-
Hello
this is the link to the sheet with names replaced for this excercise by a student number.
https://app.smartsheet.com/b/publish?EQBCT=19be3add8fa043718d77d37695992b50
i want to run the students horizontally so i can send the row to the driver, showing who their students are for that trip.
Joe
-
Have you tried Cutting and Pasting the student? When I cut and paste the student from row 6 into row 7 the value is changing down to 11 students.
-
Hello Mike
Yes, that is the way to go i reckon - i was kind of fixed on the idea of dragging the cell, and the idea of cut and paste didn't really come in to my little brain,so thanks - a very simple solution!
Joe
-
I still don't see the problem with dragging.
-
Hello Craig
how strange....as you say, dragging is now ok...yet when i posted this the dragging operation also dragged the function down to include the next row, therefore was not reducing my number of students on the bus...
but as long as it works, then happy days!
thanks for your input.
Joe
-
Very strange. If you can reproduce, I would love to see it -- either video or screen share.
The only thing I can image is that you were editing the formula at the time of the drag, but that does not make sense from a work flow perspective.
Craig
-
I would love to see that too!
-
I've been thinking about this.
I have a few questions:
1. Is this a sheet for today/tomorrow only or can you plan farther in advance?
2. you have 4 buses with designations. you have 8 drivers (numbered 1-8).
do you really have 8 drivers?
3. I assume there are 4 routes (1 per bus) and that each route is assigned the same bus day to day / week to week.
4. do students have an assigned route or can they change bus day to day / week to week?
Thanks for humoring me.
Craig
-
I know this is old but I figured out how to reproduce the changing formula issue in the linked sheet.
If you drag one of the cells specifically listed in the formula (The first & last cell), the formula changes to adapt. If you drag any other cell (in between) covered by the formula, it works as intended. One potential workaround is to add columns for blank cells to reference in the formulas, making all student cells 'drag-able'.
