Forum Discussion
Lookup/Autofill function
I opened a new excel sheet. In one tab I have a list of company names. In the other tab I would like to go to any row in a column and start typing one of those companies names from the other tab and it will just fill in the rest. For example I start typing PE and it knows I mean PEPSI so it fills it in. PEPSI would be listed on the other tab. How do I do this? I would be doing this in column C rows 3 though 50. So then the next row I start typing a different company and it fills it in. What is the formula for this?
4 Replies
- You can actually achieve this using a drop-down list.
Select the range C3:C50 then select ‘Validation’ from the Data menu > Select list in the Allow option drop-down > click in Source Control and select the range, on the other sheet, which includes your list of vendors (you can also choose here whether you want to show the drop-down arrow in the cells so you can choose the vendor from the list - check the In-Cell Drop-down box).
Note: If you have two entries which start withe same letters, you will not be given a suggestion for the entry until you reach the first unique letter..... e.g. in a list of countries containing United States of America, United Arab Emirates and United Kingdom; when typing in United Excel can’t differentiate between the three, only when you type the first letter of the second word, I.e. the S of States, would Excel offer auto-fill for USA. Kathy -- someone else may have a more direct answer (more satisfying)....So far as I'm aware, there is no FORMULA or FUNCTION for what you're asking. There conceivably could be a macro or VBA routine, but that seems like overkill at this point.
If I were sitting down with you face-to-face, I'd have a bunch of questions before we went any further. Such as
- What is the bigger picture here? What is the purpose you're working to serve?
- What else are you expecting on sheet two, once "Pepsi" gets filled in? Are you going to be adding data about "Pepsi" or are you wanting data to be retrieved from somewhere? Where?
- Will "Pepsi" (using it as a stand-in here for any name, just as you did) ...will "Pepsi" being appearing only once on sheet 2, or is it possible it will appear multiple times? If the latter, what will be different about the multiple occurrences?
Why ask all those questions? Well, it's not that I'm nosy. Really. But how we approach answering your very first question might change depending on that bigger picture, the ultimate purposes, etc.
mathetes Just looking to fill a sheet with vendors every week that we paid. So this week I paid 20 different companies. Next week I paid 30.......some the same as last week. Just the name on the sheet and that is it. I just don't want to have to keep typing every single vendor name each week on a spreadsheet. If there is an auto fill or something....
If that's really ALL you're doing, just a list of names and you start over each week with mostly the same names, then a simple solution is to keep it all on one sheet. Excel is generally "smart enough," when you're using a lot of the same names (same texts) and repeating them in the same column, to prompt you with the complete name once it recognizes a unique set of letters. Try it.
And you can just enter the week (the monday or friday of whatever) once and copy to the other entries for that same week.
But just to underscore here, you're really not using Excel for any special purposes, using any of its data analysis purposes or anything like that, right? If you are, if at the end of the year you want to know how many times you'd paid "Pepsi" for example, we'd possibly want to get more sophisticated.
