Changing a tab name in real-time based on cell value from another worksheet

[yeshallfind]

Hi guys. This sounds like a simple question and I've been searching and searching, but can't seem to find the right answer.

I currently have a spreadsheet with 2 tabs/worksheets named SHEET1 and SHEET2.

Basically all I want is if anything is entered in cell A1 of Sheet, then the tab on Sheet 2 name changes accordingly.

So A1 on sheet 1 is called APPLE, then tab on sheet 2 is also called APPLE.

and if So A1 on sheet 1 is called ORANGE, then tab on sheet 2 is also called ORANGE.

Hope this makes sense.

PS. There is a lot more to it, but I just want to get a grasp of this before asking my next question.

Thanks very much.

 

[JonXL]

Sorry, I forgot to add that the output should be something like Cells 2 out of 10

Does it need to be VBA?

If not this will work:

="Cells "&COUNTA(Sheet1!A1:A10)&" out of 10"