Creating Forms, but jumping data?

Hello everyone,

First time posting here, but I came to you because of a project I'm currently working on.

I have a bunch of fields ranging from name, address, payments, check-out date, etc. on a single "form" that I created on an excel sheet.

In the boxes adjacent to these fields, I've inputted formulas to pull data from a different sheet within the same workbook.
(for example, =IF(List!Y2=0,"No",IF(List!Y2=1,"Yes",IF(List!Y2=2,"Unconfirmed")))

My current problem though is that when I select the entirety of this form, and drag down the bottom right corner, it skips a bunch of rows.
For example, I'd expect the {=IF(List!Y2=0,"No",IF(List!Y2=1,"Yes",IF(List!Y2=2,"Unconfirmed")))} section to become {=IF(List!Y3=0,"No",IF(List!Y3=1,"Yes",IF(List!Y3=2,"Unconfirmed")))}
but instead it jumps to {=IF(List!Y49=0,"No",IF(List!Y49=1,"Yes",IF(List!Y49=2,"Unconfirmed")))}

For the record my "form" is 47 rows long, so I'm guessing it has to be somehow connected.
Is there any way I can drag the same "form" down and continue pulling data row by row?
Thank you very much for all your help!

Originally Posted by :) Sixthsense :)

When dragging down you might be having hidden rows between this formula

=IF(List!Y2=0,"No",IF(List!Y2=1,"Yes",IF(List!Y2=2,"Unconfirmed")))

to this

=IF(List!Y49=0,"No",IF(List!Y49=1,"Yes",IF(List!Y49=2,"Unconfirmed")))

Unfortunately, that isn't the case.
None of my rows are hidden.
I have multiple rows that pull data from the other worksheet, starting at row 3 to row 47.

Sorry for the delayed response, I just had to erase some information before anything else.

What I'm essentially trying to do is select A1 to I47 on sheet1 and pull down using the bold cross that appears at the bottom right.
If you try to do it though all the values switch from "List!H2" to "List!H49" instead of "List!H3" (of course the H is just an example cell value)

Haha, I think I just did that right before you posted ^^

Originally Posted by :) Sixthsense :)

Sorry to say that still I am unable to understand your requirement

In sheet 1, select A1 to I47. Then using the "autofill" function by dragging I should be able to replicated the form underneath starting at A48.
When I do, all the fields are replicated perfectly except for the cells that require information from the Sheet titled List.

Originally Posted by JBeaucaire

1) Put this reference formula in A1: =CEILING(ROW()/47, 1)+1

2) Now change over to and INDEX() formula to pull values from the other sheet. For instance here are some sample formulas, you can add the missing using these techniques

B3: =INDEX(List!H:H, A1)
C3: =INDEX(List!I:I, A1)
G3: =IF(INDEX(List!J:J, A1)=0, "x", INDEX(List!J:J, A1))
C7: =INDEX(List!V:V, A1)

E17: =CHOOSE(INDEX(List!Y:Y,A1)+1, "No", "Yes", "Unconfirmed")
E19: =CHOOSE(INDEX(List!Z:Z,A1)+1, "No", "Yes", "Unconfirmed")

B26: =IF(INDEX(List!CO:CO, A1) ="N", "x", INDEX(List!CO:CO, A1))
E26: =IF(INDEX(List!CP:CP, A1)="N", "x", INDEX(List!CP:CP, A1))
B28: =IF(INDEX(List!CQ:CQ, A1)="N", "x", INDEX(List!CQ:CQ, A1))

B30: =IF(INDEX(List!BL:BL, A1)=300, "Yes", "No")

D33: =SUBSTITUTE(SUBSTITUTE(INDEX(List!BJ:BJ, A1), "N", "No"), "Y", "Yes")
D39: =LOOKUP(INDEX(List!AY:AY, A1), {"Blank","N","Yes"}, {"x","No","Yes"})


3) Now you can copy A1:I47 and paste below as many times as you wish, each new section will use the next row from the LIST.

You are a savior.
But before you leave me, haha I'd really appreciate a bit more explanation?
For step one, which sheet am I supposed to add the formula to? and more importantly, what exactly is it doing/how will it effect anything?

Originally Posted by JBeaucaire

1) Put this reference formula in A1: =CEILING(ROW()/47, 1)+1

2) Now change over to and INDEX() formula to pull values from the other sheet. For instance here are some sample formulas, you can add the missing using these techniques

B3: =INDEX(List!H:H, A1)
C3: =INDEX(List!I:I, A1)
G3: =IF(INDEX(List!J:J, A1)=0, "x", INDEX(List!J:J, A1))
C7: =INDEX(List!V:V, A1)

E17: =CHOOSE(INDEX(List!Y:Y,A1)+1, "No", "Yes", "Unconfirmed")
E19: =CHOOSE(INDEX(List!Z:Z,A1)+1, "No", "Yes", "Unconfirmed")

B26: =IF(INDEX(List!CO:CO, A1) ="N", "x", INDEX(List!CO:CO, A1))
E26: =IF(INDEX(List!CP:CP, A1)="N", "x", INDEX(List!CP:CP, A1))
B28: =IF(INDEX(List!CQ:CQ, A1)="N", "x", INDEX(List!CQ:CQ, A1))

B30: =IF(INDEX(List!BL:BL, A1)=300, "Yes", "No")

D33: =SUBSTITUTE(SUBSTITUTE(INDEX(List!BJ:BJ, A1), "N", "No"), "Y", "Yes")
D39: =LOOKUP(INDEX(List!AY:AY, A1), {"Blank","N","Yes"}, {"x","No","Yes"})


3) Now you can copy A1:I47 and paste below as many times as you wish, each new section will use the next row from the LIST.

Never mind, I understand now.
Once again Thank you.
You are amazing.

Originally Posted by JBeaucaire

Once you get into the habit of using INDEX() formulas, you'll never go back. The power and flexibility of INDEX() to look up values in 1 and 2 dimensions is very useful.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.

Got it!
Thank you!