Creating Forms from Spreadsheet List

Occasional Visitor



I have a two sheet excel document - sheet one is multiple rows of data in a list form, sheet two I need to create one page forms of exact template where I insert a cells from sheet one. I have the forms set with five pages in a line, I need 96 pages total. On the first page cell B7(=Sheet1!F2), B9=(Sheet1!B2), B11=(Sheet1!G2), B13=(Sheet1!J2). The second page falls in Column F and needs to prefill with the same pattern from Sheet 1 but using row 3, and so on... all the way to row 96. Is there a way to make this automatic with a formula? I would be so happy to learn how to save time doing this. 




Thank you!

1 Reply

@logicalplease Where is the third page? Are the pages aligned horizontally? Every page is  4 columns to the right of the previous page? If so, take a look at the attached file.


Sheet2 has formulas starting in B7 and copied across. The formula is different in each row, because it returns text from a different column in Sheet1.


Copy the formulas across as far as required to get to the data in row 96 (my sample does not have that many rows in Sheet1). 


Then you can copy all the columns and use Paste Special > Values to replace the formulas with the text that they returned. This will get rid of all the formulas that are in the empty columns. 


Let me know if that works for you.