Help to automate excel spreadsheet

**mmoore5553** · 2019-11-17, 08:47 AM

I need to automate having a list of users and adding certain verbiage and taking in the variable names. I was going to use mail merge but i need it to stay in excel sheets

if you look at the code it just has a heading and then some words with the variables dropped in. I have added a result page on how i want it to look. Where the data is does not matter so much. Just trying to automate this. If there is an easier way that I am missing then let me know.

I am new to scripting and trying my best.

the results page should have the info that is provided and the script will go back and get the next user and then have her info in the spreadsheet. It should do that for x amount of people in A column.

Below I have attached the document to review

arraytest.xlsm

**Bob Phillips** · 2019-11-17, 01:40 PM

You don't need VBA, you can do it with formulas.

In B16 add data validation, with a type of List, and a formula of
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

In B18, add a formula of
=VLOOKUP(B16,Sheet1!A:G,7,FALSE)

You can select the child from the DV list and print it off or whatever, then the next.

**mmoore5553** · 2019-11-18, 01:41 PM

unfortunately that will not work for me. I have tons of files that i will need to do this too. I will need something to loop through the person and then go to separate tabs. But thank you bob for that insight.