Using tables, lists, and dropdowns and not sure how to make this work…

[DianeDennis]

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

[#2]

@DianeDennis 

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.

[#3]

@mathetes 

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=11472...

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

[#4]

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)....

[#5]

Thank you SO much, truly! 🙂

And I'm jealous, I haven't had a Mac since 2017. Couldn't afford it when I needed a replacement. Stroke a few keys for me (hmm, that almost sounds kinda naughty, lol). 😉

I hope that your vacation was super great!!

Thank you again!!
Diane

[#6]

@DianeDennis 

OK, Diane, here's a first pass at a revised workbook. A couple of pointers: in general, all the text books on Excel advise strongly against doing the kind of formatting that you've done on your time sheet--in particular the practice of merging cells, making small columns so as to be able to create small check boxes (or whatever your goal was).....the merged cells across a row can make writing formulas tricky in the first place, but over and above that you'll have all kinds of problems when the time comes to revise.

And definitely don't do fancy formatting before you finish the basic functionality. After it's working, if it makes sense to combine header rows, fine, but don't go overboard even then.

It would also be good for you to do some research on tables. You had created a number of tables but each consisted of one data element. I eliminated those and created them anew as single tables on employees and a single table on projects. A solid table collects all relevant information about a given category--for example, employees. So I've created an employee data base (EE_DB) and it's very easy to write VLOOKUP formulas that access all the data for any given employee from a single entry (I've chosen SSN).

Then there's the matter of data elements in a table and here the cardinal rule is DO NOT COMBINE disparate elements into a single cell/column. Separate identifiers (e.g., SSN) from name; separate first from last name; etc. Excel can easily combine first and last name (to print a paycheck or name card, for example); and it's easy to do first name first for the name card, last name first for an alphabetical list of employees, etc.  

And that applies to addresses too. Definitely don't combine address with name; separate street address (most databases have two lines for street address, to accommodate apartment numbers and the like); separate City from State and ZIP.... and so forth.

I would also separate marital status from number of exemptions.

Try, on the data collection screen (WH347) to collect everything for any given employee's workweek on one line. You'll see I spent quite a bit of time modifying your layout. I didn't finish, figuring I've given you enough pointers on how to design....the remainder should be fairly easy for you. But if you get bogged down or have any questions, by all means come back and ask.

[#7]

Oh gosh, I feel bad that you spent that time revising the form. Thank you so much for that time but I'm locked into the layout at least for the foreseeable future. 😞 It's beautiful, I wish I could use it.

It was originally created simply to be the starting point for first a "fill-in-by-hand" form and then a fillable PDF, and I'm locked into making it work as best I can with the visual layout as is.

Will I be able to use your solutions above even though I can't change the visual layout?

I can change things like separating identifiers (e.g., SSN) from name; separating first from last name; separating the street address; marital status & exemptions; changing column widths; unmerging cells; etc. if it doesn't change the visual layout of the form - it's just the visual layout that I can't change.

Thank you again so much!
Diane

[#8]

@DianeDennis 

Will I be able to use your solutions above even though I can't change the visual layout?

The tables as I designed them with the VLOOKUP function to access the data will work with any form. You will have to change the cell references some. 

I would challenge your  "I'm locked in" statement (attributed to the fact that it was created to be a manual form and then a PDF); it's entirely possible to make it a better Excel design that could still be used for filling in by hand or as a PDF. The way that it was approached in Excel is what I was challenging in that doing it that way is bound to lead to Excel problems if you also are planning to use it as an Excel spreadsheet.

If all you were doing is creating a form -- using Excel to accomplish that would be doing it the hard way, IMHO; you'd be better served by using Word or some other program; you (or somebody else) DID use Excel, however, so I'm trying to get you (or that somebody else) to use Excel more appropriately.

The ideal resolution might be to use BOTH your original for the manual form AND a revised form such as the one I began for Excel. Trying to make the one serve both purposes will, I fear, result in neither purpose being well served.

[#9]

@mathetes 

Thank you! I'm excited to get going with it. 🙂

If I understand you, I completely agree with you that there could be a better layout for the Excel design. The issue is that it's based on government reporting requirements and the government created the original PDF layout as an example of what a contractor can use to submit his payroll (using what program initially I don't know).

All the government wants is the information, they don't care how it's formatted but most contractors feel that they must use the form layout that the government put out as an example.

That's what I mean about being locked into the visual layout. If I use a different visual layout, no matter how nice it is, it'll be shunned by many due to their thinking that it won't be accepted by the government and fear that their payments will be withheld.

Those same contractors want that payroll form in Excel format though, rather than in a PDF version.

So I recreated it in Excel (if that's the proper terminology) and over the years I've improved it as I've learned more. It's been being used by federal contractors across the country for many years now but I totally agree it's most definitely not what it could be, either in layout or design (column widths, merged fields, etc.) or functionality (but I have been told by others that they have been able to make their own changes to it without trouble).

I invite and relish all help that you are willing to give me to use Excel appropriately, and I thank you. 🙂 I absolutely love being able to continue to improve these workbooks/sheets as well as I love the happy emails I get from contractors who enjoy the little calculations I've added. Every bit of help that I get here turns into so many people being happy, so again, thank you! 🙂

Knowing why I have to stick with the same visual layout, are there still ways to do this while using Excel more appropriately to accomplish what I'm trying to or is it as good as it gets so to speak in this situation?

I want to do it right but I have to do it in Excel and it has to be the same visual layout.

I also do want it to function as a spreadsheet as much as a form, with the form printing in the visual format that it appears.

I apologize if I'm misunderstanding, that's my forte, but I'm trying. 🙂

And a follow-up question, you have me intrigued about Word now. Many, many years ago I recreated this form in Word (on my Mac even!) but I couldn't figure out formulas (if that's the correct terminology) to make the entire form calculate as the Excel worksheet does. The calculating functions in Word seemed very crude compared to Excel and wouldn't perform as I needed.

Is there a way to make Word perform calculations that are as complex as Excel? I realize I might need to post this in the Word forum, and/or perform a few searches on it, but your comment intrigued me.

Thank you again so very much for your help! I'm excited to try this out!

Again welcome home to you and I hope you get some R&R (transition time) from your vacation before you have to go back to work. 🙂

Diane

[#10]

@DianeDennis 

Again welcome home to you and I hope you get some R&R (transition time) from your vacation before you have to go back to work.

Thanks for your well wishes, Diane. The fact of the matter is that I'm in my early 80s, have been retired for just over twenty years, so although my wife and I keep very busy with multiple activities, none of them are income producing (don't worry; we're doing fine!). We spend the summer in a home along the New Jersey shore.

Is there a way to make Word perform calculations that are as complex as Excel?

No. Not to my knowledge. There are ways to embed an Excel sheet in a Word document, but I don't think that's what you should be doing.

Hearing the history now, I can see why you don't want to change that form. HOWEVER, if I were in your shoes, I would work to create an approved Excel spreadsheet for the use of those who want to use Excel to record hours (and all the associated data), and do it as a clean Excel spreadsheet. I can't believe that a clean and clear spreadsheet couldn't be created, and be far more reliable that working to make that one you have serve the purpose. It wasn't what it was designed for in the first place.

[#11]

@mathetes 

Hi!

Thanks for your well wishes, Diane. The fact of the matter is that I'm in my early 80s, have been retired for just over twenty years, so although my wife and I keep very busy with multiple activities, none of them are income producing (don't worry; we're doing fine!). We spend the summer in a home along the New Jersey shore.

That sounds very nice! I'm glad you two get to enjoy that!! 🙂

I'm sorry I couldn't get in here yesterday to thank you, there was maintenance going on here in the forums I guess.

I had typed out here what I was going to ask but then realized that I completely missed something you said in your first help to me and that was that you made a couple databases.

I spent all day yesterday creating tables and lists and whatnot to recreate what you'd done (or so I thought, lol) and no matter what I did I couldn't make it work like yours does. Soooo very close but no cigar...

So I spent this morning defining my lists and tables like I had them prior to changing them to try to follow your example and still no luck.

So many errors coming up through all this, delimited list errors, etc., lol.

Then it finally clicked with me when I opened the Name Manager that EE_DB means database and you had mentioned databases in your first help to me (my brain takes a very long while sometimes to click things together), and that's when I realized that your tables aren't simple tables/lists.

I've managed to use your database by adding my 347 to your workbook, changing some of the info in the tables/databases, and copying the various formulas that you've used. Thank you!! 

🙂

1. hmm, something I've done while revising the info in the tables/list/database is causing #Value errors now when I delete the info in the employee field on the 347. I think I have to start over. lol, I've retyped this message so many times over past couple days as I've worked through this and I think I've just massively confused myself.

I'll come back here once I've figured out where I went wrong with the #Value error.

Okay, whoo-hoo, I'm back with my first question... (I just realized that my smiley faces don't make it in here - I am grinning with happiness over what you've helped me to accomplish!)🙂

In your version of the 347, when I look at the "Data > Data Validation" I see that you've got "List" and =Employees for a few of the fields ("Exemptions" for example).

When I go to the Exemptions field on my 347 and select "Data > Data Validation" and select List, I have to manually enter =Employees in the source box. But then I get an error that "A named range you specified cannot be found". How do I make mine work like you made yours work?

You have been so wonderful! I'm tickled over what you've helped me to put together. Thank you again so much and have a great day!

Diane Dennis 🙂

[#12]

https://club.excelhome.net/forum.php?mod=viewthread&tid=567518&extra=&mobile=

How about this?

[#13]

Hi,

Thank you. I couldn't figure out how to make it English on my browser.

Thank you,
Diane

[#14]

@DianeDennis 

https://support.microsoft.com/en-us/topic/use-microsoft-translator-in-microsoft-edge-browser-4ad1c6c...

Edge 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.

[#15]

Thank you! 🙂 Unfortunately, when I tried to open it in Chrome I landed on a red page and it said "Dangerous webpage blocked". I was going to upload a screenshot to show you but I guess I can't upload here. I'm sorry.

[#16]

@DianeDennis

really? I guess your anti-virus softwate may block the download.

I have no problem to download the attached.

How about download with your Android device then copy to PC?

 I can not upload .zip file here so that I have to share the link.

Because it includes a standalone .exe file probably。

[#17]

@DianeDennis 

When I go to the Exemptions field on my 347 and select "Data > Data Validation" and select List, I have to manually enter =Employees in the source box. But then I get an error that "A named range you specified cannot be found". How do I make mine work like you made yours work?

You shouldn't be using Data Validation at that point in the 347 Form; it's a VLOOKUP formula that gets the value, just as do all the others, once you've entered the SSN. Here are the formulas that were in my worksheet. 

• For Last Name: =IFERROR(VLOOKUP(A17,EE_DB,2,0),"")
• For First Name: =IFERROR(VLOOKUP(A17,EE_DB,2,0),"")
• For Exemptions: =IFERROR(VLOOKUP(A17,EE_DB,4,0),"")
• For Title (or your equivalent): =IFERROR(VLOOKUP(A17,EE_DB,5,0),"")
• and so forth.

A central idea of a data TABLE is that you can reference all of the columns with VLOOKUP or one of many equivalent data retrieval functions (XLOOKUP, INDEX and MATCH paired), simply by referring to the key (in this case the SSN) and then the offset number of columns. Using Data Validation for each column is to invite error. Just make sure the first entry is correct, and the table is consistent with relevant info for that person, and the rest follows from that.

Data Validation has been cleared from that first set of columns in this version, and you can see how it works.

By the way, did you overlook that I'd created those new tables?!

[#18]

Hi!

By the way, did you overlook that I'd created those new tables?!

I totally remembered your tables, I even used them, thank you so much. 🙂

I started a new workbook with your workbook, and then added another worksheet with my original design 347 form, and then tried to follow along from there with what you’d done while trying to make it work in the layout that I’m currently using.

I spent all weekend and really was making some progress, ooh I was so tickled with how it was going {big cheese-eating grin}, but then I messed it up Sunday afternoon. Crash. Burn. lol...

I revised the column for SS so that it contained the employee’s name and SS, and got that to work perfectly (but I must have deleted it when I was cleaning up after my crash & burn, so I have to do that again).

I then added a few columns to the EE_DB:

"Fringe Benefits" (differing but flat $ amount for each employee), and
“Withholding Tax” (differing percentage rates for each employee), and
“Tools” (differing but flat $ amount for each employee)

And I got the “Fringe Benefits” and “Tools” to work great on the 347, but then I started getting #Value errors in the "Gross Amount Earned" field when I’d delete the employee selection from the dropdown list in the employee name/SS field (if that makes sense). I checked your version and that doesn’t happen, so I know I messed something up.

Maybe adding columns or maybe trying to do the Data Validation is where I messed it up. I’m going to try again without the data validation to see what I can accomplish. {smile}

I did save a copy of the last version of what I was doing. Would it be alright if I upload it to show you what I’ve managed to accomplish (and the #Value error that I get when I delete an employee name on the 347)?

I’m guessing one of the things that went wrong is that I tried to do the “Lists” but also the other is I may have messed up the EE_DB when adding the three columns. Something I did is causing the #Value errors, just not sure what.

Thank you again so much for all of your help and your time!
Diane

[#19]

@mathetes 

Oh my gosh! I figured out the #Value error! I looked to see what you had in the "Gross Amount Field" and it was slightly different than mine so I edited mine and ... no more #Value error!

You are the best!! I can't tell you how tickled I am with how this is coming out. Thank you SO MUCH!

I'd love to show you what you've helped me to accomplish so far if you'd like to see it.

My next step is to figure out how to get the "Withholding Tax" fields on the 347 to multiply the percentages in the "Withholding Tax" column (that I added to the EE_DB table) against the "Gross Amount Earned" field that gets manually entered on the 347.

I got the flat rates (e.g., "Tools") to work; now for those pesky percentages.

Should I post that in a different post or does it still fit here?

And if I do need to put that in a separate post, how do I choose which of your answers here to select as the answer?

Thank you again so much, Mathetes!!

Diane

[#20]

@DianeDennis 

I'd love to show you what you've helped me to accomplish so far if you'd like to see it.

I would be very happy to see it. And I'd suggest posting the link right here in this thread, since it is a continuation.