Solved

Best way to populate an Excel form from an Excel table

Posted on 2014-02-23
4
197 Views
Last Modified: 2014-03-18
Hello,

I'm looking for some suggestions on the best way to populate a form from a database or table in Excel. The intent, after populating the forms, is to send them to their respective named individuals for proofreading and updating of the information. Here's the table:

aThe tricky part for me is that the table is grouped by family (there are several hundred) and then within the groups, sorted by head of the household, spouse (if any), children (if any) in order of age. So in the table, column A is a general index and column B is the family numbering.

For example, the first five entries (red index 1-5) are all in the Baggins family with Dad listed first, mom listed second, and the three kids following in order. Red index 6 is Mr. Fresh Forest who is single with no kids, and the last four entries (red index 7-10) are the Grimp family.

Note that the information in columns C-F is common to all members of a particular family and the content of the remaining columns (G-O) is specific to individuals.

Here is the form which I'm hoping is self-explanatory. (By the way, I'm sending it as an Excel file and as a PDF so that people can print it and make comments by hand if they want):

bThe family name is simply the combination of the parents' names or single head of household. The form is set up so that members of a family are identified by the number of their position in the family from column B in the table. Therefore, section "INDIVIDUAL #1" could be blank, contain the spouse's information or the oldest child's information depending on the makeup of the family.

This table sample shows data in all fields however the actual database is full of blank cells and therefore the respective form fields will be blank.

Of course, when the forms are completed and returned, the information will need to be put into the database manually — unless of course someone knows of a very slick way to sort of import the updates.

Thanks
0
Comment
Question by:WeThotUWasAToad
4 Comments
 
LVL 1

Expert Comment

by:cben
ID: 39882601
Try this.
No time now to explain - just that data is in Table so new rows are added automatically.
Choose specific family in top row of output sheet.
Hope it helps
Chris
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 39882958
If you're sending as an Excel file, why not just keep the info in the table format you have now?  Doing that will make it much, much easier to write a macro later to automatically harvest the edits...
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 39885397
Might be worth considering a true database or at least a true database format within Excel.

If in Excel each group would have a common id field, for example the Baggins family, rather than having individual id's 1 to 5 and then family id 1 to 5, they would have the individual id's 1 to 5 and would also have a family group id to append to their id within family eg

Record       Family#       Family Group    Name
1                BAGGINS1   BAGGINS           Dad
2                BAGGINS2   BAGGINS           Mum

You can then sort/group on Family group ID and create records accordingly.

If using Access, I am sure it would be a fairly simple to create a form to represent the data bu I don't have a copy of Access to hand to try.

Thanks
Rob H
0
 

Author Closing Comment

by:WeThotUWasAToad
ID: 39938480
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now