Share via

How do I create review forms in Excel?

Anonymous
2023-04-11T19:09:56+00:00

I have an interesting data review puzzle that I am trying to solve.

I am working on a historical research project that includes a large Excel database of public employees. I am trying to create a system using Excel forms to review those records and indicate which are correct.

Each row indicates a record of employment. Some people have only one record, many have 2-3, and a smaller percentage have >4. Each person has a unique ID. For example, if there are four job records for John Doe, each record includes John's unique ID number (something like Person.0001). Each record then includes additional information like job title, record year, place of employment, etc.

I have approximately 30,000 individual records of employment for approximately 13,000 individuals, and I am trying to create a process for reviewing their accuracy. What I would prefer is a form that shows the person's name and ID number and then lists the content from the individual record of employment. Keeping with the John Doe example, it would look something like the following:

NAME: John Doe

ID: Person.0001

Job 1: Widget-maker, New York City, 2000

Job 2: Widget team supervisor, Jersey City, 2004

Job 3: Widget division director, work-at-home, 2010

Correct?

Notes:

"Correct" would have a checkbox, or at least a text box where I could type Y or N.

Notes would allow brief text notations.

Once I've reviewed each of these, I would want the system to let me know which records are accurate and which are not.

Unfortunately, the data is sufficiently elaborate that I cannot reduce each person to a single row. For example, I cannot have John Doe's three jobs listed on the same row, but rather as three separate rows.

If Excel cannot be configured to provide this sort of review form, can anybody recommend another product?

Thanks for your help.

Microsoft 365 and Office | Excel | For education | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

13 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-04-12T19:07:12+00:00

    Thanks for the quick reply. This is great. I didn't know how to use advanced filters. But this doesn't appear to do what I'm looking for.

    I'm trying to have Excel produce a form that would list all of the records for one individual. Once I review that information, I can move on to the next one. Put another way, I'm trying to find a way to do this work in an environment that is NOT just a list of individual rows. I've tried this way, but the data is sufficiently similar that it's easy to make mistakes.

    One way to think about it is that the datasheet is the back-end with the all the information. The form is the front end pulling all of the data for rows containing an ID number. So the form would so all the data from all the rows with ID.00001. I can then push a button to proceed to everything with ID.00002.

    Does that help? The real issue for me is trying to get a better idea of what Excel forms can do as a front end for existing data rather than as a data entry tool.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-04-13T18:54:14+00:00

    Thanks for writing. Here is some sample data. In this case, I'm using the real career details of some real historical figures from the early U.S. government. The names may look familiar. This samples contains a few fields. My actual data contains a few more fields.

    After the table of sample data, I've created a simulation of what I'd like the form to show for one of these people (the first one, John Quincy Adams).

    I hope this makes things more clear.

    SAMPLE DATA

    ID Full Name First Name Last Name Job Title Department Division City State Country Start Date End Date
    ID.0001 John Quincy Adams John Adams Secretary of State State Department Central Office Washington DC US 1817 1825
    ID.0001 John Quincy Adams John Adams Minister State Department Foreign Service Berlin Germany 1797 1801
    ID.0001 John Quincy Adams John Adams Minister State Department Foreign Service The Hague The Netherlands 1794 1797
    ID.0001 John Quincy Adams John Adams Minister State Department Foreign Service St. Petersburg Russia 1809 1814
    ID.0001 John Quincy Adams John Adams Minister State Department Foreign Service London England 1815 1817
    ID.0001 John Quincy Adams John Adams President of the United States Cabinet Washington DC US 1825 1829
    ID.0002 Alexander Hamilton Alexander Hamilton Secretary of the Treasury Treasury Department Central Office Philadelphia PA US 1789 1795
    ID.0003 John Marshall John Marshall Secretary of State State Department Central Office Washington DC US 1800 1801
    ID.0003 John Marshall John Marshall Chief Justice Judiciary Supreme Court Washington DC US 1801 1835
    ID.0004 William Clark William Clark Territorial Governor State Department Territorial St. Louis MO US 1813 1821
    ID.0004 William Clark William Clark Indian Agent War Department Indian Affairs St. Louis MO US 1807 1824
    ID.0004 William Clark William Clark Superintendent of Indian Affairs War Department Indian Affairs St. Louis MO US 1824 1838
    ID.0004 William Clark William Clark 2nd Lieutenant War Department Army OH US 1792 1795
    ID.0004 William Clark William Clark Captain War Department Army OH US 1795 1796
    ID.0004 William Clark William Clark 1st Lientenant War Department Army US 1803 1807

    SAMPLE FORM

    Name: John Quincy Adams<br><br>ID: ID.0001<br><br> <br><br><br> Job Title Department Division City State Country Start Date End Date <br> --- --- --- --- --- --- --- --- <br> Secretary of State State Department Central Office Washington DC US 1817 1825 <br> Minister State Department Foreign Service Berlin Germany 1797 1801 <br> Minister State Department Foreign Service The Hague The Netherlands 1794 1797 <br> Minister State Department Foreign Service St. Petersburg Russia 1809 1814 <br> Minister State Department Foreign Service London England 1815 1817 <br><br><br> <br><br>Correct? [yes/no checkbox or space to type Y or N, with results recorded in spreadsheet]<br><br> <br><br>Notes? [Data entry box, with text recorded in spreadsheet]
    0 comments
  3. Anonymous
    2023-04-13T03:12:44+00:00

    Unfortunately, the data is sufficiently elaborate that I cannot reduce each person to a single row.

    ??

    can you provide some data and your expected result?

    re:

    NAME: John Doe

    ID: Person.0001

    Job 1: Widget-maker, New York City, 2000

    Job 2: Widget team supervisor, Jersey City, 2004

    Job 3: Widget division director, work-at-home, 2010

    Correct?

    Notes:

    editable form?

    Then write back worksheet?

    what is your original data structure?

    0 comments
  4. Anonymous
    2023-04-13T02:27:53+00:00

    You can find help with VBA code to create a user form which meet your goal in Stack Overflow.

    0 comments
  5. Anonymous
    2023-04-12T06:10:19+00:00

    You can use advanced filter to get a list you want to review.

    Here is a sample. You can also use ID as criteria.

    Result:

    Best Regards,

    Snow Lu

    0 comments