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

@mathetes 

Oh, outstanding, thank you!! 🙂

I'm off to start a new thread about why my dropdown lists are appearing the way they are on my daughter's computer and if there's a way to change it. 🙂

Thank you again and have a terrific evening!!

DianeDennis 

Here are one or two references to "Helper columns" to help you in your thinking.

mathetes 

Oh, that's excellent! I was thinking (hoping?) that might be a possibility. Thank you!

That's very interesting about the helper columns. There have been times when I thought something like that would be useful but I didn't really have a super-strong need ever to pursue it. Very nice to know that capability exists!

And, lol, that's me... creative (or more like "bull in a china shop"). 😉

Thank you again!

DianeDennis 

One of the things we "experts" sometimes advise--to avoid overly complicated formulas--is to create what are called helper columns. The helper columns, which can be hidden from the user once everything is working, are where interim processes or calculations are performed, with the results used by other helper columns or, ultimately, displayed in the "visible portion" of the workbook.

In essence, what you're suggesting is that on a bigger scale: a helper worksheet, performing things behind the scenes, with the clean results displayed on the printable "final" worksheet.

It's a workable idea (actually quite creative) given the constraints you've been given. It does present the risk of being inadvertently messed up by somebody who doesn't realize what's going on. So you will want to investigate and use (if you're not already) spreadsheet protection.

Out of curiousity, if the 347 worksheet was set up "like a spreadsheet should be" would I have been able to accomplish my most recent desire, trying to get the real name (column a) to appear in the cell even though the dropdown list was mapped(?) to the coded name (column b)?

If I understand what you're doing, yes. Essentially you're creating that aforementioned "helper column"

Thank you so much! I too have enjoyed myself (may have even uttered a couple squeals of joy, lol). 🙂

I have a thought running around in my head about having two 347's in the workbook, one laid out like you've said it should be (347-A) and the one that I'm currently stuck with using (347-B).

When I'm ready to pursue it and I have questions about it I'll go ahead and start a new thread but I wanted to mention it here to you since you've been helping me so much with this. 🙂

What I was thinking is that 347-A would be the one that does all the work (like the 347-B is currently doing) and because it's laid out like a spreadsheet should be everything will work properly, including the more complex issues.

The cells on the 347-B could then have simple references to the 347-A worksheet instead of needing complex formulas, etc.

The user would fill in the 347-A worksheet but print the completed-automatically 347-A worksheet.

Out of curiousity, if the 347 worksheet was set up "like a spreadsheet should be" would I have been able to accomplish my most recent desire, trying to get the real name (column a) to appear in the cell even though the dropdown list was mapped(?) to the coded name (column b)?

Thank you again, Mathetes! Have a great day!!

DianeDennis 

I shall continue the fight and figure things out one way or another. 😉

Thank you again so much, Mathetes

You're welcome, for sure. It was (and continues to be) an interesting challenge. I'm still available if you want to post more questions. You might want to start a new thread with a clear new question or two. And make sure you include a link to the then current version.

DianeDennis 

Diane, as we've been over, I think it's entirely possible that you (your organization) are expecting too much of this one spreadsheet--for it to serve as a printed template to be filled in manually AND to serve as an Excel spreadsheet backed up by a data table, to do a lot automatically. A good deal of that "too much" stems from it having been originally designed solely for the purpose of printing, and as such it utterly mangles all kinds of principles of good spreadsheet design.

Okay, for this particular issue, a user might have 1 employee that works under three (or however many) different classifications and therefore has to be listed 3 different times on the spreadsheet, with the accurate info for each classification (pay, etc.).

Just to tackle this dimension: 1 employee, no matter how many different hats she or he may wear, is going to have the same SSN, same withholding, same benefits. I suppose it's possible--though unlikely?--that there may be different pay rates based on what hat is being worn. But the point is that much will remain the same, so it'd be a better design--far better--to keep the primary identity the same (name, SSN, etc) and just make the job/task/"hat" different and the hours apportioned to that job, task or hat.

Truly, Diane: While wishing I could be more optimistic, I think you (the organization for which you work) should be pursuing getting an expert in to do this and that expert should start from scratch. In my humble opinion. (And I'm not offering myself for the task; I'm retired, 81 years old, enjoying a summer at the beach.)

DianeDennis 

I need the user to be able to select the "CodedEmployeeName" from the dropdown list in A17 on the WH347 but have it populate with the "EmployeeName" rather than the "CodedEmployeeName".

If I understand what you "need" here, you are saying you want, in cell A17, to select something like "CODE1234" but have "Harrison, George" appear also in cell A17. That's not possible. It probably would be possible with a VBA routine (which is not my area of expertise), but I'd be wary of that as a solution for a variety of reasons.

If I'm misunderstanding your request, please clarify. And if you can, post another copy of the current workbook...I'm looking at my most recent, but strongly suspect that is not really helpful.

mathetes 

Hi Mathetes!

I hope this finds you well!

I need to take everything you've helped me with here a step further and I'm hoping you can help me. 🙂

I added a column next to the "EmployeeName" column (A), titled "CodedEmployeeName" (column B).

I need the user to be able to select the "CodedEmployeeName" from the dropdown list in A17 on the WH347 but have it populate with the "EmployeeName" rather than the "CodedEmployeeName".

I tried this:

Selected cell A17 on the WH347.

Clicked on the "Formulas" tab in the Excel ribbon.

Clicked on "Name Manager."

Created a new name called "RealName."

Entered the following in the Refers to field:

=INDEX(Tables1!$A$2:$A$501, MATCH(INDIRECT("WH347!A17"), Tables1!$B$2:$B$501, 0))

With cell A17 still selected, I went to the "Data" tab in the Excel ribbon, and selected "Data Validation" from the "Data Tools" group.

In the "Data Validation" dialog box, I selected the "List" option from the "Allow" drop-down menu.

In the "Source" field, I entered the following:

=Tables1!$B$2:$B$501

Unfortunately, it's still displaying the coded employee name from column B instead of the real name from column A.

What have I missed? Or have I totally messed it up? I finally quit because it just kept getting crazier and crazier, lol.

Thank you so much, Mathetes!! 🙂

DianeDennis 

Yesterday when I deleted a couple of columns from the EE_DB, I had to manually change the references on the 347 sheet. Is there a way to delete columns/rows that will automatically update the references on the 347 so that it doesn't have to be done manually or is that just a part of how it works?

Certainly when you're using VLOOKUP, where you refer to the column to be retrieved by offset number, I think you need at the very least to review the formula to make sure that your deletion hasn't changed that offset. The. bigger lesson would be to design the table up front so that you're not needing to delete columns, in particular not deleting columns from the early or central part of the table. You can always add columns toward the end (right end) of the table.

If you've not read it, this resource on Excel Tables would be worth keeping as a reference. The same site has materials on all of the VLOOKUP function, along with others (INDEX and MATCH, for example) that can also be used. Each method has its advantages.

mathetes 

Hi!

I thought I posted a response here yesterday but it's not showing up. I probably forgot to hit the "post" button...

Thank you SO MUCH for your time and help and for what you helped me to accomplish and learn!!

Oh, I did post; I remember that I included info on what I did to accomplish something else on the sheet. Maybe I entered something wrong and it didn't approve my post..

I figured out how to make the withholding percentages work! Using your code and other code that I had that wasn't quite right, I managed to put it together. Now the withholding box on the 347 calculates using the percentages listed in the EE_DB table.

Yesterday when I deleted a couple of columns from the EE_DB, I had to manually change the references on the 347 sheet. Is there a way to delete columns/rows that will automatically update the references on the 347 so that it doesn't have to be done manually or is that just a part of how it works?

Again, thank you so much, mathetes!!

Diane

DianeDennis 

It's looking nice. I made a small change in the first entry in the FICA column: since we "named" cell L2 in the tables sheet "FICA"  you can actually put that name into the formula. One of the big advantages of named ranges is using names to make formulas more legible. 

Let me know if you want more help with it....I am a big advocate of people learning through figuring out for themselves, even if it takes some trial and error.

mathetes 

Thank you!

I just learned how to do this the other day when I uploaded the first one here so fingers crossed that I remember how it works, lol...

https://docs.google.com/spreadsheets/d/1bnbDf_Nn8myBRMIWKRyVEo8UDZ66Hxcu/edit?usp=drive_link&ouid=114726432609057347750&rtpof=true&sd=true

Thank you again!

Diane

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.

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

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

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?!