This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I'm having trouble creating a master data sheet that is filled according to the data entered on a form within the same workbook. The only problem is that the form has to be in a certain format where data is not all on one row or string. For example, I need to pull the Lot Number in cell A5, Oil Type in B5, Source Tank in C6, Measurement Temp in D5, and Length Measurement in D7, then by using cell references, pull all of this data into a single row on the master data sheet. Now I understand that I can just do =A5, =B5, =C6, =D5, =D7, etc. along the entire master data sheet row, but this is where my question comes into play, how can I get these cells to then reference A11, C12, and D11 by auto filling those formulas into the next row of the master data sheet? I would essentially need these same formulas to follow the trend of skipping 6 cells whenever I autofill them to the next row.
Master Data Sheet:
| ='Form'!A5 | ='Form'!B5 | ='Form'!C6 | ='Form'!D5 | ='Form'!D7 |
|---|---|---|---|---|
| ='Form'!A11 | ='Form'!B11 | ='Form'!C12 | ='Form'!D11 | ='Form'!D13 |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
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.
Answer accepted by question author
This part will get a seuquence like this one when you fill down this formula. Index(columnA, ROW(A1)*6-1) will get A5,A11,A17...
=ROW(A1)*6-1
Thank You!
Can you break down that formula for me a bit? I find INDEX to be kind of confusing and am trying to apply it to the rest of the sheet.
=INDEX(Form!A:A,ROW(A1)*6-1)
=INDEX(Form!C:C,ROW(A1)*6)
=INDEX(Form!E:E,ROW(A1)*6+1)