Forum Discussion
Using tables, lists, and dropdowns and not sure how to make this work…
Hi!
I have an Excel workbook with several worksheets that need to be filled in by the user.
The first worksheet (Sheet A) has fields for employee names/addresses/id#s, their work classification, their tax filing status, their gross pay, the various taxes that come out, etc.
There is also a worksheet titled "Data Sheet" with a table ("Table A") that has the employee names/addresses/id#'s.
When filling in "Sheet A", the user can select from a dropdown list of those employee names/address/id#'s (the list comes from "Data Sheet - Table A") and the employee's info fills into that field.
These are the steps I followed to set up the "Data Sheet" and the dropdown list on "Sheet A".
1. Added a worksheet to the end and named it Data Sheet.
2. To add a table, made a list of employee names with a header name and then selected an item in the list and Insert>Table, named the table (no spaces) in the Table Name box, and made sure that the header row is checked.
3. Next, I defined the list. Clicked on an item in the list, then under Formulas, selected Define Name.
4. Gave the list a name different than the table name or head name. In the reference section, highlighted all the items, but not the header. This allows the list to be added to without having to change the range.
5. On "Sheet A", I selected the cells I want to use the drop-down for. Selected Data Validation on the Data tab. Selected allow “list”, then in the source box hit F3 so that the lists I've made would pop up and selected the correct one.
6. Made sure that the column where users will input their info is unprotected.
I've since set up a second table ("Table B") on "Data Sheet" and defined the list, etc., to be the employee fica rate.
On “Sheet A” the fica withheld from the employee's paycheck goes into the field next to the employee name/address/id# field.
When the user selects an employee name/address/id# from the dropdown list on “Sheet A”, is it possible to make the corresponding fica field auto-calculate from “Data Sheet” at the same time?
The list/table (“Data Sheet – Table B”) would need to be the percentages that each employee pays and then “Sheet A” would need to pull the corresponding percentage from “Data Sheet – Table B” and multiply it with the wages the user has entered and the result of that calculation would land in the “fica” field.
Thank you so much!!
Diane
- Hi,
Thank you. I couldn't figure out how to make it English on my browser.
Thank you,
DianeEdge and chrome can translate Chinese langage to English.
http://e.anyoupin.cn/EData/?p=tools.ceshi.index/downloadTreeFile&file=/excel/EntryAssis41.zip
see the attached if this stand alone drop down help to pick data.
Could I request that you post a copy of this workbook on OneDrive or GoogleDrive? Rather than try to re-create our own versions from your description, a copy of the actual--with false names and addresses, but otherwise an accurate representation of your actual--would make it a LOT easier for us to help you.
Post that mock-up on OneDrive or GoogleDrive with a link pasted here that grants access to it.
Hi Mathetes!
Absolutely and thank you so much! lol, I've never done that before so I had to figure that out before I could do it. 😉 I hope this works!
Here's the link: https://docs.google.com/spreadsheets/d/1V-jG9URNdJNiaveOeLMvtx_hs1bd61yw/edit?usp=sharing&ouid=114726432609057347750&rtpof=true&sd=true
I put notes in bold on the Data Sheet laying out what I'm trying to do. When I posted my question(s) yesterday I only asked about one thing I was trying to do. The notes show everything that I'm trying to do (I think it's all basically the same, just that there's more than one list/table/fields involved).
Again, thank you so much!! 🙂
Diane- OK--I've downloaded the file to my Mac and will be taking a look at it as soon as I can. (Just got home from a vacation trip, so there are other things to do for a bit)....
