98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel advanced - List reference shorthand
excel advanced - List reference shorthand
Resolved · Low Priority · Version Standard
Fri 15 Dec 2006, 10:00Delegate Hannah said...
Hannah has attended:
Excel Intermediate course
List reference shorthand
The main sheet on my workbook has several columns for which entries can only be from a specific list on another worksheet within the book. This ensures uniform entries which is important as the workbook template is used in several offices around the country. My question is: is there any way that a shorthand or acronym key can be developed for the lists to make enteries more quickly? Example - The list on sheet 2 may look like this:-
Newspaper (Daily)
Newspaper (Weekly)
Television
Magazine (Weekly)
Magazine (Monthly)
Radio
Can a key be developed so that on sheet 1 it is only necessary to write ND, NW, T, MW, MM, R in the cell and for Excel to fill-in the entry fully?
Fri 15 Dec 2006, 10:42Trainer Richard said...
RE: list reference shorthand
Not as a standard excel setting. You could probably acheive this using VBA, and that could be complex and expensive.
You could however use something called VALIDATION. It is taught on our advanced course.
This allows you to specify a LIST of cells that contain the entries you require. This eliminates the need to type the entry in, AND allows you to limit the entries to that list only!/
There is one limitation. THe list may only be specified on the SAME worksheet. Yoou can get round this by simply linking a set of cells to the original list using the = key.
To find VALIDATION, Select DATA, then VALIDATION on the menu bar.
Theoption you want is LIST. Specify the cells you want to show in the list by selecting them, or otherwise you can simply type the values you want in , separated by a comma.
ie:
Newspaper (Daily),Newspaper (Weekly),Television,Magazine (Weekly),Magazine (Monthly), Radio
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Page Break Preview in Excel 2010 (Hint/tip)If you select View then Workbook Views then Page Break Preview, you will be able to view how your Excel spreadsheet will be split across multiple pages when printed. Even better, you can also drag a page break to a new place. Excel will then scale down your entire worksheet to fit the information you want on the pages you want. |
