Solved

Best way to populate an Excel form from an Excel table

Posted on 2014-02-23
4
199 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 33

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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