Excel fill in a form with pick list or drop downs, also need calendar pick
I have a form where I would like to automatically fill in with
Name - from a drop down list
P-Card - based on the name
Date - open a calendar with default to current date
and
have a drop down, with defaults, for G/L # and Cost Center fields
See Attachment
Receipt_Form.xlsx
Name - from a drop down list
P-Card - based on the name
Date - open a calendar with default to current date
and
have a drop down, with defaults, for G/L # and Cost Center fields
See Attachment
Receipt_Form.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Roy,
Just FYI, that failed here when I opened it in Excel 2016 64-bit.
~bp
Just FYI, that failed here when I opened it in Excel 2016 64-bit.
~bp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. Roy I liked your calendar - for printing reasons but you forgot to have the P-card field.
Shums, thanks for the example and being so quick.
Shums, thanks for the example and being so quick.
If in fact you want only the last 8 digits of the P-Card number then the formula (in C3) should be =IFERROR(RIGHT(VLOOKUP(C$2
Hi Bill, I don't use 64-bit. I've never needed to. The declarations can be changed to run in 64-bit.
Can you test this?
Receipt_Form.xlsm
Can you test this?
Receipt_Form.xlsm
You're welcome ssblue! Glad I was able to help.
Shums,
FYI, Pop-up Calendar not in my base install of Excel 2016 64-bit...
~bp
FYI, Pop-up Calendar not in my base install of Excel 2016 64-bit...
~bp
ASKER
I would like to have more information on how you did what you did so I can learn how to do it for myself.
Roy,
Error resolved and calendar functioning.
~bp
Error resolved and calendar functioning.
~bp
Bill.
You need to change your laptop then. LOL.
You need to change your laptop then. LOL.
ssblue, the p-card number can be added.
Shums, the pop up addin is not distributed with Excel. It looks like your example uses a datepicker control, which as I said will not work in all versions of Excel
Shums, the pop up addin is not distributed with Excel. It looks like your example uses a datepicker control, which as I said will not work in all versions of Excel
ASKER
How do I add the P-Card info?
Hi ssblue,
Please paste below formula in C3:
Please paste below formula in C3:
=IFERROR(VLOOKUP(C$2,Data!$A:$B,2,0),"")=IFERROR(VLOOKUP(I$2,Data!$A:$B,2,0),"")=IFERROR(VLOOKUP(C$20,Data!$A:$B,2,0),"")=IFERROR(VLOOKUP(I$20,Data!$A:$B,2,0),"")
Bill & Roy,
That pop calendar is not an add-in: please check this link how to get it from control:
How to Insert a Calendar When Clicking on a Cell in Excel
That pop calendar is not an add-in: please check this link how to get it from control:
How to Insert a Calendar When Clicking on a Cell in Excel
Or go to the Developer tab->Insert, choose the more control icon in the lower right-hand corner of the ActiveX controls and select the "Microsoft Date and Time Picker..." control.
Thanks Bill.
ssblue, I've added some explanations post back if you need further help
ssblue, I've added some explanations post back if you need further help
Hello Martin,
No big deal, but that control doesn't show in that selection list on my system either.
~bp
No big deal, but that control doesn't show in that selection list on my system either.
~bp
Click the icon that the cursor is pointing to and then scroll down.
Yup, that's where I was Martin...
~bp
~bp
From the web:
AFAIK this control is contained in mscomct2.ocx, which would typically be in c:\windows\system32
If you can find that file (whereever it is), then it probably needs to be registered. If you are using 64-bit Windows, you might need to do more. Either way, see e.g.
http://social.msdn.microsoft.com/Forums/is/sbappdev/thread/91cf3127-70fe-4726-8a27-31b8964430c5
(If you are using 64-bit Office, I do not know what the situation is with this particular control).
ASKER
How about a short simple explanation of how to add a drop down selection list.
Martin,
The Common Controls can no longer be used with 64-bit applications, like Excel 2016 64-bit, so that's the deal there. And for some of those controls like the calendar MS has not provided an alternative. Folks fashion there own in a form...
~bp
The Common Controls can no longer be used with 64-bit applications, like Excel 2016 64-bit, so that's the deal there. And for some of those controls like the calendar MS has not provided an alternative. Folks fashion there own in a form...
~bp
DatePicker controls are not available on all computers, this is well documented. This has been true for several versions of Excel.
My last attachment shows instructions for creating the drop down lists within the workbook.
My last attachment shows instructions for creating the drop down lists within the workbook.
Please find attached...
Receipt_Form_V1.xlsx