picklist or autocomplete?

hollywoodnorth

New Member
Joined
Oct 12, 2002
Messages
1
How do I create a picklist that I can use on several different pages in same workbook? If there is another solution a way to apply autocomplete to all pages in a workbook not just one data is initailly on I'll go for that too.

Thanks
This message was edited by hollywoodnorth on 2002-10-13 22:26
This message was edited by hollywoodnorth on 2002-10-13 22:28
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
If your picklist is only very small, I think you could use Data Validation / Allow = List (type the options directly into the Source box, separated by commas) - but I think you would need to set it up separately on each sheet. If the picklist is longer, requiring a range on the spreadsheet itself, I think you could still use Data Validation but with some repetition to overcome the Excel 97 limitation which only allows a List range on the same sheet as the Validation.
Find a blank range common to all sheets for use as your picklist ranges. On the first sheet enter all the picklist options into the selected range. On the other sheets, refer each cell of the range to the corresponding cell in the range on the first sheet thus populating the range in each sheet.
Name each range with a similar name (eg picklist1, picklist2, etc.)
Set up Data Validation on each sheet with the same settings except for the range name used in the Source box.

This solution is a little clunky but not too difficult to set up and no VBA.
HTH
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use the same list in all your data validation dropdowns if you name it eg MyList. Then in the Data Validation Source box type =MyList.
 

Forum statistics

Threads
1,143,709
Messages
5,720,391
Members
422,283
Latest member
Parvathy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top