Pull dynamic data from a Master Sheet to populate a Different Sheet via form

Jerry Nevins

Hello:

I have a Master sheet (database) where I have columns A thru HM. I pull a number of different reports from this master sheet and information is added by folks via one of 12 different forms on a daily basis. What I am looking for is this--I have created a form in a new sheet, where a person is able to look for and find their full name, I would like this name to updated dynamically and linked to my Master sheet, the person enters answers two other questions and when they hit submit, it pulls the rest of their demographic information and populates this testing sheet, so that I can run a daily testing report in the format the State requires for daily result submission. I am only looking to pull 11 columns, of my master sheet that would be used to auto populate when the form submission is completed and I would auto populate the rest of the needed columns as that information is static or is the answered questions.

Currently, I pull reports to create testing sheets so that the people can find their name and enter the data and time of the testing done manually; then someone has to manually enter the date and time of the testing into the spreadsheet and then compile this spreadsheet for submission to the State.

I have attempted to link and index, as it is not dynamic and the form wants to populate the information into the master database and not pull information and populate in the testing sheet. Cell linking does not work as the rows are always being added and the row are also alphabetized on a regular basis.

Any assistance that could be provided would be much appreciated.

Thanks.

Dale Murphy

@Jerry Nevins Does this master sheet have one entry for each person that might be testing? (I don't fully understand why there are twelve forms submitting new rows to a master list; usually my "master lists" are more static as far as new rows.)

In broad strokes (If the form submitting to the testing sheet has information in it that can be reliably matched to the master sheet):

• in the testing sheet I would use index formulae to retrieve the information to populate the eleven columns you need.
• I would have at least one more column to date stamp when the form submission was received (using a workflow that detects a new row).
• I would then use a MOVE ROW workflow to take that complete submission and put it into a third sheet that becomes your Submission to the State sheet.

dm

Jerry Nevins

Dan,

Thanks for your answer, but I am not sure I understand what you are suggesting. If I am understanding things, the index function pulls everything from a column and copies it to another column in a different sheet. and it is based on a static row position within the sheet. Since I routinely reorganize the sheet by last name, that would break the linkage and since it dynamic, I would not be able to have it populate correctly after each reorganization. Is this understanding correct?

What I want to do is put in the testing sheet "John Doe" and have the master sheet pull John Doe's demographic information and populate it to the corresponding columns for just John Doe's row. Do you know if this is possible?

Jerry