A simple? autofill question

Closed
gj - Apr 4, 2010 at 11:17 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 5, 2010 at 03:57 AM
i have a set of questionnaire questions and answers one below the other on an excel worksheet (c. 200 sets of c. 100 data items). by chance each set is 100 rows long. i wish to extract the actual anwers (which are 1 or 0) into a simple table.

i set out 3 rows of cells referenced directly to the original data (sorry no vert line on my keyboard...)
=c3 \ =d3 \ =c6 \ =d6 \ =e6 etc.
=c103 \ =d103 \ =c106 \ =d106 \ =e106 etc.
=c203 \ =d203 \ =c206 \ =d206 \ =e206 etc.

I then thought I could autofill for the other 200+ rows, but exel instead restarts

=c6 etc.
=c106 etc.
=c206 etc.

Is there any way of making it continue
=c303
=c403
etc.

It seems so simple and I never thought it might not work.

Thanks for any help.

1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 5, 2010 at 03:57 AM
i wonder whether this will help you
in A1 copy this formula
="c"&3
in A2 type this formula
="c"&RIGHT(A1,LEN(A1)-1)+100
copy A2 down

you will get
c3
c103
c203
c303
c403
c503
c603
c703
c803
c903


if you want the actual values of the cell C3,c103 etc. then
after typing the above formulas A1,A2 A3 etc
in B1 type this formula
=INDIRECT(A1)
copy B1 down
then B1 will have C3 value
B2 will have c103 value
B3 will have c203 value
etc.

can you use this input for other cells also.
0