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 :)

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

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

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