locking a formula

joe taylor

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

 

Mike Wilday

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. 

 

joe taylor

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

J. Craig Williams

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

Mike Wilday

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. 

joe taylor

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

Mike Wilday

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. 

joe taylor

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

J. Craig Williams

I still don't see the problem with dragging.

 

joe taylor

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

J. Craig Williams

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

Mike Wilday

I would love to see that too!  

J. Craig Williams

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

Michael Derry

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'.