Can AutoComplete Contain a Cell Reference

I have a stack of descriptions above C24.  Occasionally I want to reference text from another cell inside the AutoComplete sequence.

Is this even possible?
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Rob HensonFinance AnalystCommented:
As the question is phrased, the answer is no.

The cell entry options are:

1) AutoComplete - as you type Excel will assess the cells in a list immediately above and when it has only one option from the list above it will suggest the remainder of the entry for you. Pressing Enter when the text is highlighted will accept the suggested entry.

2) Pick From List - Right click on a cell at the bottom of a list and Excel will generate a list of the entries above, if the list already includes duplicates the selection list will only show one entry. Click on the entry required.

Neither of the above will generate a cell reference to the text above.

Rob h

Experts Exchange Solution brought to you by

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
Jeffrey CoachmanMIS LiasonCommented:
Bill Golden,
Can you give us a very specific example of what you are trying to accomplish.?
Perhaps posting an Excel file illustrating what you need...
Bill GoldenExecutive Managing MemberAuthor Commented:
Well, I had to do some experimenting and actually, I figured out it can be done - sort of!  
AutoComplete looks above the resulting cell contents, not the underlying formula, if a formula is used.
if cell A3 contains the text ACME Products, the text displays as ACME Products and AutoComplete responds to the letter A..C..M, etc.
if cell A4 contains ="ACME Products "&F12&"" and cell F12 contains the phrase (Warehouse Location), cell A4 displays ACME Products (Warehouse Location) and
Autocomplete initiates display still based on A...C...M, etc, not ="ACM...etc.

If you are entering data in A10 and AutoComplete inserts ACME Products (Warehouse Location), A4 will update when you change or remove the contents or cell F12, but not A10!  

Guess it makes sense...
Bill GoldenExecutive Managing MemberAuthor Commented:
Rob, you sent me down the path to accomplish what I needed to accomplish.  Thanks,

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.