automate excel

hi,

I have a long long excel to update everyday and I want to make the same cell/position can be directed automatically, like if I update cell A and then I will go to update cell B, I do it everyday, is there any configuration in eXcel can do that once I update cell A, it will JUMP/Switch to Cell B for me to update ?

also any way to make excel 2010 lock down some cells, just in case i update the wrong cell which I don't suppose to do it, the result is , when I modify that cell I don't allow/suppose to , I will find out that I can't update it.

any idea?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Mike in ITIT System AdministratorCommented:
You can lock individual cells in Excel:
  1. Navigate to the Review tab
  2. Click Protect Sheet: enter a password and select what you want to allow users to do if anything
  3. Click Ok: no the whole sheet is locked
To unlock certain cells:
  1. Select all cells you don't want to have locked
  2. Right-click on your selection
  3. select Format Cells
  4. Click on Protection tab
  5. Uncheck the "Locked" checkbox
  6. click Ok

As for automatically moving you to the next cell that you need to update, that will depend on what you are updating.
Are you putting the same data in all the cells?
Where are you getting the data?

It might be possible to auto-update the cells directly from your source.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Are you putting the same data in all the cells? "

no

"Where are you getting the data? "

can be from other cell! or input manually !

"You can lock individual cells in Excel:
Navigate to the Review tab
Click Protect Sheet: enter a password and select what you want to allow users to do if anything
Click Ok: no the whole sheet is locked
To unlock certain cells:
Select all cells you don't want to have locked
Right-click on your selection
select Format Cells
Click on Protection tab
Uncheck the "Locked" checkbox
click Ok"

so don't turn on the WHOLE sheet protect we can't run lock on individual cell ?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Navigate to the Review tab
Click Protect Sheet: enter a password and select what you want to allow users to do if anything
Click Ok: no the whole sheet is locked"

yeah, the whole sheet is locked, all cell is locked, but how can I only locked some cell (with formula inside) only ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mike in ITIT System AdministratorCommented:
You have to turn on sheet protection to have any cells locked. If you want to only have a few cells locked you can select the whole sheet and follow my steps above for unlocking them, then turn on sheet protection and go and lock the individual cells.

  • select whole sheet
  • right click
  • select Format
  • select Protection tab
  • Uncheck the "Locked" checkbox
  • click Ok
  • Navigate to "Review" tab
  • click "Protect Sheet"
  • enter a password and select what you want to allow users to do if anything (it defaults to selecting cells whether locked or not)
  • click Ok
  • select cells you want to lock (can use ctrl + click or do one at a time)
  • right click selection
  • select Format Cells
  • select Protection tab
  • check the "Locked" checkbox
  • click Ok
Now your cells will be locked, but not the whole sheet. You do have to turn on sheet protection to have cells locked.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"You have to turn on sheet protection to have any cells locked.:

hi,

did you try it in excel 2010 ?  mine seems doesn't works.

once I done that:

select cells you want to lock (can use ctrl + click or do one at a time)
right click selection
select Format Cells

Open in new window


I can't select "format Cells" anymroe and it is greyed !
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

it seems that I have to turn on and off protect sheet and protect workbook feature, then I come back and turn on protetct sheet again for it to work.

one point, I can protect cells has formula/equation, right? I want to protect cell with formula and only allow cell can change.

how about the other thing. one I update a cell, is there any feature in Excel automatically jump me to the NEXT cell I supposed to edit ?
0
 
Mike in ITIT System AdministratorCommented:
There is no built-in feature that will move you to the next cell that you need to update. You can create that effect with some VBA code, but that will take time to figure out and it will be based on cell updates in certain cells.

How many updates are we talking here? 10, 20, more?
Does the order of the cells to be updated matter?
What cells need to be updated(they will have to be set to be watched manually)?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"How many updates are we talking here? 10, 20, more? "

15

"Does the order of the cells to be updated matter? "
yes

"What cells need to be updated(they will have to be set to be watched manually)?"

a group of cells, I keep doing it everyday and too time consuming.
0
 
Mike in ITIT System AdministratorCommented:
Something that you could do then is to create a User Form for those 15 cells. You would enter the information in the User Form, then VBA would put that info into the specific cells for you.

It could look as simple as this.
UserForm1.PNG
If you give me the list of cells and a copy of your workbook I can help with writing the VBA. This workbook would then have to be saved as a macro-enabled workbook (.xlsm)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so in Excel without this kind of form there are no way to automatically move to the NEXT cell ?

must through this ?
0
 
Mike in ITIT System AdministratorCommented:
I'm sure that you could also write some VBA that might do it, but it would have to run through calculations on the sheet a lot and catch everytime you type something into the cells specified. That would slow Excel down a lot, especially if you have a lot of data and/or a lot of formulas.

The simplest and fastest way to do this would be a User Form like I presented above. It doesn't have to look like that one you can format it any way you like.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
" That would slow Excel down a lot, especially if you have a lot of data and/or a lot of formulas.
"

not have a lot of data, very little but very slow by hand. I want to avoid a lot of human interaction.

OK, so just one method, VBA.  right ?
0
 
Mike in ITIT System AdministratorCommented:
Both ways that I mention do require VBA yes.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.