Thursday, February 26, 2004
Well I got 2 out of 7 things done for the night...
Maybe I will just post an Excel tip of the day for ya and then I can say I did three things on my list of seven. Round that up to 50% and you can say I nearly completed my to do list or is that stretching it a bit far?
Anyway Excel tip follows.
Maybe I will just post an Excel tip of the day for ya and then I can say I did three things on my list of seven. Round that up to 50% and you can say I nearly completed my to do list or is that stretching it a bit far?
Anyway Excel tip follows.
Excel Tip of The Day
Validation Links or how to use nice little selection dropdowns in your spreadsheet
Did you ever wonder how to put a nice little dropdown box for selections in your spreadsheet?
Well I will show you here.
First you need to set up you sheet to handle this. I generally use a utility sheet for background stuff that doesn't need to be seen or made pretty. I usually name the sheet "lists".
On that sheet I will make lists in individual columns (without a header). Lets use the example of Months. So in the sheet lists, column A rows 1 to 12 I would type January through December. In this case the list is limited to these twelve items. I will also use an example of a potentially longer list. Eg Years. So in column C rows 1-15 I might enter 2004 to 2018.
I will treat these slightly differently.
The next step is to name the ranges. (This is not actually necessary but makes it a lot easier to work with)
First let's name the months. Select Lists!A1:A12. Now from the menu select Insert, Name Define. In the name section type "month" (without the quotes) and then click Add or hit Enter. Note that the actual range was already entered as the selection, and was absolute "lists!$A$1:$A$12" There is a lot of room to play with this - but that is for a more advanced topic another day.
Now lets name the years range. This time select the entire column C. (Do this by clicking on a cell in column C then CTRL+SPACE, or by clicking on the C at the top of the column.) Now from the menu select Insert, Name Define. In the name section type "year" (without the quotes) and then click Add or hit Enter. This names the range as the entire column, so if at some point we want to add years 2019 onward we can with no other changes necessary for the dropdown to still work.
Now go to the sheet where you want the drop down. This might be as a selection for a report month, a data entry field or similar. Select the required cell(s). From the menu select Data, Validation. In the settings Tab, click the dropdown on the allow field and choose "List". In the source field type "=month" (without the quotes). Click OK.
Its done! Click on the cell and you can see the drop down button. Click on that (or ALT+Down-Arrow) to view the options from the list, in this case January through December.
Repeat for the year cell with "=year" in the source field. Note how you can scroll down in the dropdown box past the end of the entered data. If this is not desired then use the same method of naming the range as for the months.
The are plenty of options you can play with in the Data Validation dialog box. Clicking the help (F1) while in there will also help you learn more, but the simple instructions above will give you an incredible amount of flexibility.
Use this to:
Turn laborious data entry into simple selection from dropdowns.
Ensure uniformity of data entry of common items which will improve formula driven functions such as SUMIF, VLOOKUP, SUMPRODUCT etc.
Improve your use of Sort, Subtotals and Filters.
Any questions or comments welcome.
Validation Links or how to use nice little selection dropdowns in your spreadsheet
Did you ever wonder how to put a nice little dropdown box for selections in your spreadsheet?
Well I will show you here.
First you need to set up you sheet to handle this. I generally use a utility sheet for background stuff that doesn't need to be seen or made pretty. I usually name the sheet "lists".
On that sheet I will make lists in individual columns (without a header). Lets use the example of Months. So in the sheet lists, column A rows 1 to 12 I would type January through December. In this case the list is limited to these twelve items. I will also use an example of a potentially longer list. Eg Years. So in column C rows 1-15 I might enter 2004 to 2018.
I will treat these slightly differently.
The next step is to name the ranges. (This is not actually necessary but makes it a lot easier to work with)
First let's name the months. Select Lists!A1:A12. Now from the menu select Insert, Name Define. In the name section type "month" (without the quotes) and then click Add or hit Enter. Note that the actual range was already entered as the selection, and was absolute "lists!$A$1:$A$12" There is a lot of room to play with this - but that is for a more advanced topic another day.
Now lets name the years range. This time select the entire column C. (Do this by clicking on a cell in column C then CTRL+SPACE, or by clicking on the C at the top of the column.) Now from the menu select Insert, Name Define. In the name section type "year" (without the quotes) and then click Add or hit Enter. This names the range as the entire column, so if at some point we want to add years 2019 onward we can with no other changes necessary for the dropdown to still work.
Now go to the sheet where you want the drop down. This might be as a selection for a report month, a data entry field or similar. Select the required cell(s). From the menu select Data, Validation. In the settings Tab, click the dropdown on the allow field and choose "List". In the source field type "=month" (without the quotes). Click OK.
Its done! Click on the cell and you can see the drop down button. Click on that (or ALT+Down-Arrow) to view the options from the list, in this case January through December.
Repeat for the year cell with "=year" in the source field. Note how you can scroll down in the dropdown box past the end of the entered data. If this is not desired then use the same method of naming the range as for the months.
The are plenty of options you can play with in the Data Validation dialog box. Clicking the help (F1) while in there will also help you learn more, but the simple instructions above will give you an incredible amount of flexibility.
Use this to:
Turn laborious data entry into simple selection from dropdowns.
Ensure uniformity of data entry of common items which will improve formula driven functions such as SUMIF, VLOOKUP, SUMPRODUCT etc.
Improve your use of Sort, Subtotals and Filters.
Any questions or comments welcome.
