Thread: uneditable cells and dynamic names of columns"

Hi Everyone.

I've recently created my first what i'd call 'complex' spreadsheet. It's for my girlfriend's fashion business. She needs to order fabric to make clothes from based on how many metres of each fabric each garment uses, and then based on the quantity of orders she receives for them.

I've got a few questions, however.

I have a few "Names" for cell validation, however when I need to add a fabric type to this column, I then need to expand the Name to include that new fabric. Is there a way this can be updated automatically?

Also, is it possible to make parts of the spreadsheet uneditable?

And, the last, and probably most complex thing. How do I make it so she can add or delete rows using a button. When adding, it inherits the formulas of the row above it. - All the totals that read from these rows also need to self-update.

Probably a big ask, but even if you could point me in the right direction, i'm happy to research.

Thanks!

I can probably help out with this one actually. If you can post the excel sheet, I can give you a better idea how to modify it when you need it.

As for making some cells uneditable, you can highlight those cells, right click, go to format, and check off the box that says "Locked". After that, just go up to Tools, Protection, Protect Sheet...

Make sure you have it only lock the "locked" cells, enter a password twice, and you're done. Anytime you go to edit those cells, it will give you an error message saying that the cell is password protected, while the other cells will be free to change, as long as you haven't locked the 'unlocked' cells.

Tim

If that doesn't answer it, reword it and I'll see if I'm still able to help.

Please can you change it to "uneditable cells and dynamic names of columns"

Originally Posted by TimHowey

I can probably help out with this one actually. If you can post the excel sheet, I can give you a better idea how to modify it when you need it.

Wow, that's above and beyond the call of duty.. thanks a lot. It's attached.

Originally Posted by TimHowey

As for making some cells uneditable, you can highlight those cells, right click, go to format, and check off the box that says "Locked". After that, just go up to Tools, Protection, Protect Sheet...

Brilliant, that's what I needed!

Basically, all I need is a way for a user of the spreadsheet to be able to add and remove rows without it affecting formulas. Is there a way around this?

Originally Posted by Samuurai

Basically, all I need is a way for a user of the spreadsheet to be able to add and remove rows without it affecting formulas. Is there a way around this?

I don't have access to a computer with Excel right now, and wont be back to work until Tuesday, but I will attempt to answer this one from the best of my memory.

Forgive me if this sounds a little,... guessy? If you have the formulas set so that everything in a column or row, you did the Fill > Down/Right, then it makes it really easy. All you have to do is select one of the Numbers or Letters on the top or left, right click, and select the "Insert" option. It will insert a row of cells, while maintaining the formulas if they were entered in that manner.

Try it out, if it doesn't work, or if the details seems vague, let me know. When I go back into work on Tuesday, I'll scope it out and see if that was right or not, then I'll take a look at the excel sheet.

Tim

Yes, I just wanted to make it a bit more indestructible. My girlfriend seems to muck it up all the time.