Auto complete (predictive fill?) a worksheet's cells across different worksheets

Hi, I am hoping that you all can help me with a problem I'm having. I have searched through dozens of posts on this website and I have been unable to find an answer so far.

I want to utilize excel's autocomplete in the same workbook across multiple worksheets. For example if I am typing in the name John Smith in worksheet 1 (row 1 column 1) I can type in J in worksheet 1 (column 1 row 8) and excel will automatically fill the cell with the name John Smith. However if i go to worksheet 2 excel wants me to type in John Smith again to get worksheet 2 to autofill the name for a different cell. How can I make excel recognize a cell's contents, for the purpose of autocomplete, across different worksheets?

Can I also reuse a master list of words/numbers of somekind that I can access for the purposes of autocomplete with different workbooks as well?

I created an example workbook/worksheets for you to check out as well.

Thank you in advance and I appreciate your time.

Edit:
I apologize for not posting this question in the "Excel - New Users/Basics" sub forum. I can't find out how to move my post but if any of the admins would like to move it I would appreciate it.

Excel creates a list of all the values in the contiguous cells in the same column, above and below the activecell. Once you have typed enough to uniquely identify the value, Excel will offer that value as the autocomplete.

But - for that to work, the values of interest need to actually be in all the columns where you want those values.

So, say you had a list of 100 names. If you wanted those names available for every column A on all the worksheets, you would need to have those names in A1:A100 of every sheet, either through copy and pasting, using links, or VBA, and you would need to start your actual data entry in row 101 instead of row 1.

Alternatives are:
1) using data validation with the list option, linked to a list of all the names of interest. But autocomplete does not work with the list unless you have already entered the name in a cell in that column.
2) using a 'name selection' userform with code to display the form, use type-ahead selection, and enter the selected value into the selected cell, whenever you select a cell in the columns where you want the list available

All those are a bit advanced, and may not work the way you want.

The problem with using the bottom of the worksheet for your list is maintaining contact with the list through contiguity while allowing new entries...