i am trying to add a column of date cells that have a drop down date rather than type it in every ti

%3CLINGO-SUB%20id%3D%22lingo-sub-1415068%22%20slang%3D%22en-US%22%3Ei%20am%20trying%20to%20add%20a%20column%20of%20date%20cells%20that%20have%20a%20drop%20down%20date%20rather%20than%20type%20it%20in%20every%20ti%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415068%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20trying%20to%20add%20a%20column%20of%20date%20cells%20that%20have%20a%20drop%20down%20date%20rather%20than%20type%20it%20in%20every%20time.%3C%2FP%3E%3CP%3EI%20have%20managed%20to%20link%20one%20cell%20to%20the%20date%20and%20time%20picker%20but%20not%20a%20column..%20help%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ealso%20is%20it%20possible%20to%20link%20the%20date%20and%20time%20picker%20to%20duplicate%20pages%20or%20do%20I%20have%20to%20add%20the%20date%20picker%20to%20each%20page%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1415068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415282%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20add%20a%20column%20of%20date%20cells%20that%20have%20a%20drop%20down%20date%20rather%20than%20type%20it%20in%20ever%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415282%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678402%22%20target%3D%22_blank%22%3E%40robinflyer%3C%2FA%3E%26nbsp%3BNot%20quite%20sure%20if%20I%20understand%20your%20request%20correctly%20but%20you%20can%20do%20the%20following%3A%3C%2FP%3E%3CP%3E1.%20In%20a%20separate%20section%20in%20your%20workbook%20(usually%20I%20have%20a%20Help%20Tab%20to%20keep%20all%20types%20of%20these%20information%20that%20would%20be%20used%20in%20other%20Tabs)%2C%20create%20a%20list%20of%20dates%20that%20you%20want%20to%20be%20included%20in%20the%20dropdown%20with%20the%20heading%20%22DATE%20SELECTION%22.%20Select%20both%20the%20heading%20and%20the%20rows%20containing%20dates%20and%20CTRL-T%20to%20convert%20into%20a%20table%20(remember%20to%20choose%20My%20Table%20has%20heading).%20Rename%20that%20table%20to%20Date_Selection%20(select%20any%20cell%20in%20that%20Table%2Fgo%20to%20Design%20Tab%2Fat%20the%20left%20corner%20there%20is%20a%20field%20Table%20Name%20and%20you%20replace%20the%20current%20name%20Table%20n%20with%20your%20name)%3C%2FP%3E%3CP%3E2.%20In%20the%20cell%20where%20you%20want%20to%20have%20the%20date%20dropdown%20(it%20could%20be%20anywhere%2C%20any%20Tab%20in%20your%20workbook)%2C%20select%20the%20cell%2FData%2FData%20Validation%2F%20choose%20List%20and%20in%20the%20field%20where%20the%20source%20list%20is%20required%2C%20type%20%3D%20INDIRECT(%22Data_Selection%5BDATE%20SELECTION%5D%22)%3C%2FP%3E%3CP%3E3.%20Once%20the%20drop%20down%20is%20shown%20properly%2C%20copy%20that%20cell%20and%20paste%20over%20any%20where%20you%20want%20to%20have%20the%20date%20drop%20down%20shows%20(or%20drag%20that%20cell%20down%20iif%20same%20column)%3C%2FP%3E%3CP%3EBecause%20the%20Date_Selection%20is%20a%20table%20in%20your%20workbook%2C%20you%20can%20refer%20to%20it%20from%20anywhere%20within%20the%20workbook%20and%20do%20not%20need%20to%20have%20it%20on%20each%20Tab%20you%20want%20to%20show%20the%20dropdown.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415549%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20add%20a%20column%20of%20date%20cells%20that%20have%20a%20drop%20down%20date%20rather%20than%20type%20it%20in%20ever%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675276%22%20target%3D%22_blank%22%3E%40hynguyen%3C%2FA%3E%26nbsp%3B%20thank%20you%20for%20your%20input%20what%20im%20trying%20to%20do%20is%20build%20a%20log%20book..%20each%20movement%20which%20requires%20to%20be%20logged%20happens%20on%20different%20dates%20so%20what%20im%20trying%20to%20create%20is%20a%20drop%20down%20date%20picker%20or%20similar%20at%20the%20beginning%20of%20every%20row%20of%20the%20log%20entry.%20i%20have%20managed%20to%20semi%20automate%20other%20entries%20with%20drop%20down%20list%20so%20that%20the%20time%20filling%20out%20the%20log%20book%20is%20kept%20to%20a%20minimum%20regarding%20typing%20in.%20ie%20places%2C%20times%20etc%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416877%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20add%20a%20column%20of%20date%20cells%20that%20have%20a%20drop%20down%20date%20rather%20than%20type%20it%20in%20ever%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678402%22%20target%3D%22_blank%22%3E%40robinflyer%3C%2FA%3E%26nbsp%3Bsorry%20I%20do%20not%20really%20get%20what%20you%20want%20to%20achieve.%20If%20you%20can%20show%20a%20sample%20of%20your%20desired%20output%20and%20available%20inputs%20with%20clearer%20explanations%2C%20it%20would%20be%20easier%20to%20suggest%20a%20solution.%20However%2C%20it%20seems%20that%20a%20macro%20would%20work%20more%20efficiently%20in%20your%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419532%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20add%20a%20column%20of%20date%20cells%20that%20have%20a%20drop%20down%20date%20rather%20than%20type%20it%20in%20ever%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675276%22%20target%3D%22_blank%22%3E%40hynguyen%3C%2FA%3E%26nbsp%3Bthe%20first%20column%20is%20a%20date%20column%20and%20rather%20than%20type%20in%20the%20date%20every%20time%20i%20would%20like%20to%20be%20able%20to%20use%20a%20drop%20down%20date%20picker%20of%20sorts.%20every%20other%20cell%20is%20created%20from%20a%20drop%20down%20list%2C%20the%20only%20manual%20input%20is%20the%20times%20and%20everything%20else%20in%20the%20workbook%20self%20populates%20from%20that%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22robinflyer_0-1590558252284.png%22%20style%3D%22width%3A%20576px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194684iBF4044FDBD637356%2Fimage-dimensions%2F576x68%3Fv%3D1.0%22%20width%3D%22576%22%20height%3D%2268%22%20title%3D%22robinflyer_0-1590558252284.png%22%20alt%3D%22robinflyer_0-1590558252284.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1422136%22%20slang%3D%22en-US%22%3ERe%3A%20i%20am%20trying%20to%20add%20a%20column%20of%20date%20cells%20that%20have%20a%20drop%20down%20date%20rather%20than%20type%20it%20in%20ever%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1422136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678402%22%20target%3D%22_blank%22%3E%40robinflyer%3C%2FA%3E%26nbsp%3BThe%20solution%20I%20suggested%20earlier%20works%20just%20fine%20for%20your%20purpose.%20Please%20see%20the%20attached%20sample%20workbook.%20You%20can%20drag%20cell%20A2%20down%20or%20copy%20paste%20it%20over%20to%20any%20place%20in%20the%20same%20workbook%20and%20you%20will%20have%20a%20date%20dropdown%20in-cell.%20If%20you%20want%20to%20change%20the%20range%20of%20available%20dates%20(currently%20I%20choose%20the%20whole%20year%202020)%2C%20just%20type%20the%20desired%20earliest%20date%20of%20your%20range%20into%20cell%20A2%20of%20Tab%20Help%20instead%20and%20if%20you%20need%20more%20than%201%20year%20of%20dates%2C%20drag%20cell%20A367%20of%20Tab%20Help%20down%20as%20you%20wish.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20note%20that%20dropdown%20list%20in%20Excel%20would%20be%20most%20efficient%20if%20your%20range%20of%20selection%20is%20not%20too%20long%2C%20otherwise%20the%20time%20you%20spend%20scrolling%20down%20to%20find%20the%20date%20to%20input%20may%20be%20longer%20than%20manually%20type%20it%20in.%20An%20alternative%20is%20to%20add%20to%20your%20cell%20a%20calendar%20dropdown%2Fpopup%20instead%20but%20it%20would%20either%20require%20a%20macro%20or%20a%20Date%20and%20Time%20picker%20control%20which%20is%20not%20very%20easy%20to%20install%20(you%20can%20refer%20to%20%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2016%2F10%2F12%2Finsert-calendar-excel-datepicker-template%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2016%2F10%2F12%2Finsert-calendar-excel-datepicker-template%2F%3C%2FA%3E).%20You%20can%20also%20consult%20an%20add-in%20for%20Date%20picker%20at%20%3CA%20href%3D%22https%3A%2F%2Fwww.rondebruin.nl%2Fwin%2Faddins%2Fdatepicker.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.rondebruin.nl%2Fwin%2Faddins%2Fdatepicker.htm%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

i am trying to add a column of date cells that have a drop down date rather than type it in every time.

I have managed to link one cell to the date and time picker but not a column.. help required to ;link several cells to the date picker. I have tried putting A1:A20 in the linked cell box in properties of the date picker but that doesn't work

 

also is it possible to link the date and time picker to duplicate pages or do I have to add the date picker to each page?

5 Replies
Highlighted

@robinflyer Not quite sure if I understand your request correctly but you can do the following:

1. In a separate section in your workbook (usually I have a Help Tab to keep all types of these information that would be used in other Tabs), create a list of dates that you want to be included in the dropdown with the heading "DATE SELECTION". Select both the heading and the rows containing dates and CTRL-T to convert into a table (remember to choose My Table has heading). Rename that table to Date_Selection (select any cell in that Table/go to Design Tab/at the left corner there is a field Table Name and you replace the current name Table n with your name)

2. In the cell where you want to have the date dropdown (it could be anywhere, any Tab in your workbook), select the cell/Data/Data Validation/ choose List and in the field where the source list is required, type = INDIRECT("Data_Selection[DATE SELECTION]")

3. Once the drop down is shown properly, copy that cell and paste over any where you want to have the date drop down shows (or drag that cell down iif same column)

Because the Date_Selection is a table in your workbook, you can refer to it from anywhere within the workbook and do not need to have it on each Tab you want to show the dropdown.

Highlighted

@hynguyen  thank you for your input what im trying to do is build a log book.. each movement which requires to be logged happens on different dates so what im trying to create is a drop down date picker or similar at the beginning of every row of the log entry. i have managed to semi automate other entries with drop down list so that the time filling out the log book is kept to a minimum regarding typing in. ie places, times etc etc.

Highlighted

@robinflyer sorry I do not really get what you want to achieve. If you can show a sample of your desired output and available inputs with clearer explanations, it would be easier to suggest a solution. However, it seems that a macro would work more efficiently in your example.

Highlighted

@hynguyen the first column is a date column and rather than type in the date every time i would like to be able to use a drop down date picker of sorts. every other cell is created from a drop down list, the only manual input is the times and everything else in the workbook self populates from that

robinflyer_0-1590558252284.png

 

Highlighted

@robinflyer The solution I suggested earlier works just fine for your purpose. Please see the attached sample workbook. You can drag cell A2 down or copy paste it over to any place in the same workbook and you will have a date dropdown in-cell. If you want to change the range of available dates (currently I choose the whole year 2020), just type the desired earliest date of your range into cell A2 of Tab Help instead and if you need more than 1 year of dates, drag cell A367 of Tab Help down as you wish.

 

However, note that dropdown list in Excel would be most efficient if your range of selection is not too long, otherwise the time you spend scrolling down to find the date to input may be longer than manually type it in. An alternative is to add to your cell a calendar dropdown/popup instead but it would either require a macro or a Date and Time picker control which is not very easy to install (you can refer to https://www.ablebits.com/office-addins-blog/2016/10/12/insert-calendar-excel-datepicker-template/). You can also consult an add-in for Date picker at https://www.rondebruin.nl/win/addins/datepicker.htm.