Please go back and re-read the OP's post.
He wants to rename the sheet tabs of Sheet2, Sheet3, etc. based on what the users enter into the cells on Sheet1.
You can't rename a sheet with a formula.
Once the OP answers the question I asked in Response #2, I'll offer a suggestion that should work for him.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.