Forum Discussion
Using tables, lists, and dropdowns and not sure how to make this work…
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
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.
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.
