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