Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Autocomplete question
Is there any way to have Excel Autocomplete based on a range in an adjacent
worksheet (not just on the rows above)???? I have a worksheet with several cells using data validation drop-down to reference the prior worksheet, but I'd also like to have the option of typing in the entry and having it autocomplete based on the list from the prior worksheet as well..... Thanks! Marika :) |
#2
|
|||
|
|||
Data|Validation doesn't work that way.
And Autocomplete uses the cells above and below, so that won't work for you, either. But you could add a combobox from the control toolbar to the cells and make it so that it works the way you want. With the combobox selected, you can show its properties. Make the .ListFillRange point at that range on the other sheet. Make the .LinkedCell point to cell under the combobox. (If you format that cell with a custum format of ;;; (3 semicolons), then it will look like it's not there, but you can still use it in other formulas.) Make the .Style fmStyleDropDownList (so the user can only use values from your list) Make the .matchentry fmMatchEntryComplete (or fmMatchEntryFirstLetter--if you like). marika1981 wrote: Is there any way to have Excel Autocomplete based on a range in an adjacent worksheet (not just on the rows above)???? I have a worksheet with several cells using data validation drop-down to reference the prior worksheet, but I'd also like to have the option of typing in the entry and having it autocomplete based on the list from the prior worksheet as well..... Thanks! Marika :) -- Dave Peterson |
#3
|
|||
|
|||
Dear Dave,
This is very useful, however I'm having trouble following the last two steps, which are the main point of my question - the .Style and .matchentry - where do I find those options? They weren't visible on the properties tab. Thanks so much for your feedback!! Marika :) "Dave Peterson" wrote: Data|Validation doesn't work that way. And Autocomplete uses the cells above and below, so that won't work for you, either. But you could add a combobox from the control toolbar to the cells and make it so that it works the way you want. With the combobox selected, you can show its properties. Make the .ListFillRange point at that range on the other sheet. Make the .LinkedCell point to cell under the combobox. (If you format that cell with a custum format of ;;; (3 semicolons), then it will look like it's not there, but you can still use it in other formulas.) Make the .Style fmStyleDropDownList (so the user can only use values from your list) Make the .matchentry fmMatchEntryComplete (or fmMatchEntryFirstLetter--if you like). marika1981 wrote: Is there any way to have Excel Autocomplete based on a range in an adjacent worksheet (not just on the rows above)???? I have a worksheet with several cells using data validation drop-down to reference the prior worksheet, but I'd also like to have the option of typing in the entry and having it autocomplete based on the list from the prior worksheet as well..... Thanks! Marika :) -- Dave Peterson |
#4
|
|||
|
|||
First, make sure you used the combobox from the control toolbox toolbar (not the
Forms toolbar). Then go into design mode (an icon also on that control toolbox toolbar). Then rightclick on the combobox and select properties. You should see it then. marika1981 wrote: Dear Dave, This is very useful, however I'm having trouble following the last two steps, which are the main point of my question - the .Style and .matchentry - where do I find those options? They weren't visible on the properties tab. Thanks so much for your feedback!! Marika :) "Dave Peterson" wrote: Data|Validation doesn't work that way. And Autocomplete uses the cells above and below, so that won't work for you, either. But you could add a combobox from the control toolbar to the cells and make it so that it works the way you want. With the combobox selected, you can show its properties. Make the .ListFillRange point at that range on the other sheet. Make the .LinkedCell point to cell under the combobox. (If you format that cell with a custum format of ;;; (3 semicolons), then it will look like it's not there, but you can still use it in other formulas.) Make the .Style fmStyleDropDownList (so the user can only use values from your list) Make the .matchentry fmMatchEntryComplete (or fmMatchEntryFirstLetter--if you like). marika1981 wrote: Is there any way to have Excel Autocomplete based on a range in an adjacent worksheet (not just on the rows above)???? I have a worksheet with several cells using data validation drop-down to reference the prior worksheet, but I'd also like to have the option of typing in the entry and having it autocomplete based on the list from the prior worksheet as well..... Thanks! Marika :) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
Office 2003 - "autocomplete" in file | open or file | save no longer works | Excel Discussion (Misc queries) | |||
Autocomplete works with my home computer but not the office computer | Excel Discussion (Misc queries) | |||
Autocomplete based on a different worksheet. | Excel Discussion (Misc queries) |