picklist or autocomplete?

[hollywoodnorth]

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

 

[BigC]

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]

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.