Forum Discussion
Using tables, lists, and dropdowns and not sure how to make this work…
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.
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!! 🙂
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!!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!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!!I shall continue the fight and figure things out one way or another. 😉
Thank you again so much, MathetesYou'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.
- Thank you, truly, Mathetes for all of your time and help!
You're correct, it's just the classification and hourly wage rate (and hours worked) that would change when the employee is wearing a different hat; he could wear a different hat multiple times a week on a project.
Unfortunately, I'm in a situation where I have to do this. I totally get what you're saying about going about it a different way but in this case I'm stuck doing it the way I am. I shall continue the fight and figure things out one way or another. 😉
Thank you again so much, Mathetes, and I wish you an absolute blast of a summer holiday at the beach! 🙂 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.)
- Hi, Mathetes, and thank you!! 🙂
Here's a link to the latest version:
https://docs.google.com/spreadsheets/d/1HqaKO7QPSB-qMDa3WQdP8YsbvifwqQuN/edit?usp=sharing&ouid=114726432609057347750&rtpof=true&sd=true
Heads up though that I think I’ve messed it all up. It was coming together so well but now I’m just beside myself, so confused, and afraid I’m going to have to start over due to my confusion. Too under the gun…
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.).
So, I was trying to have the dropdown list in A17 on the 347 worksheet reference column B on the Tables1 worksheet but when the user selects from that dropdown, I'd like the corresponding name in column A to populate A17 instead of it being populated with the name from column B.
I think the formulas that I entered though have broken it. I only did it for the first employee and now his information across the row won’t come up when his name is selected.
But, as mentioned, I may have wiped it out and have to start over so it might be more of a mess than you’re anticipating. I’m sorry about that.
Thank you again so much!! 🙂 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.