Autofill for cells in a certain pattern

[etizzle]

Dear Mr-Excel Community

I have been using excel for a while but i can hardly call myself a pro,

Anyways i would like to autofill a few cells which are in a pattern based on data in differant cells,
here is an example of the code that i am using

for cell g5 i entered =c0
for cell g6 i entered =c33
for cell g7 i entered =c65

The data that needs to go into the g column is in increments of 32
i was wondering if there was an easier way to do this using a formula/autofill as excel wont recognize this pattern,

Thanks in advance,
Ethan


I have to do this hundreds of times,

 

[AliGW]

No, sorry - I don't understand at all what you are trying to achieve, but autofill cannot fill down one column based on the contents of an adjacent column, as far as I am aware.

 

[footoo]

No, sorry - I don't understand at all what you are trying to achieve, but autofill cannot fill down one column based on the contents of an adjacent column, as far as I am aware.

Unless it is done with an appropriate formula.
For example, based on the data in post #19, enter 1 in F4, enter =IF(E4<>"",F4+1,F4) in F5 and fill down.

It seems probable, however, that the OP's actual data is different from the sample data, so the above formula is probably entirely inappropriate.
My mind reading abilities need to be improved.

 

[AliGW]

Yes, I know what you mean, and unfortunately, my crystal ball is on the blink today.

 

[etizzle]

Unless it is done with an appropriate formula.
For example, based on the data in post #19, enter 1 in F4, enter =IF(E4<>"",F4+1,F4) in F5 and fill down.

It seems probable, however, that the OP's actual data is different from the sample data, so the above formula is probably entirely inappropriate.
My mind reading abilities need to be improved.

sorry guys,
i find it really hard to explain on text,

i have a large excel spreadsheet that requires me to enter data into it,
after i enter data into it i need to create a huge matrix, each piece of the matrix must refer to a part of data entered into the data entry part on the excel document.
at the moment i am doing inspections on an apartment complex,

on the data entry part of the document i need to enter the data from the inspection aka whether or not the area showed the list of defects in the data entry section

after this i need to create a big matrix where each cell in the matrix related to a piece of data from the "data entry" section in the excel spreadsheet.

so data is displayed in patterns

in my case the apartment number was displayed on c1 then c33 then c65,

i wanted to add this data into the matrix starting at g5 and going downwards,

i hope that this sheds more clarity on the situation

 

[AliGW]

Can you give us some data (dummy) mocked up in the format you want to achieve? The problem is that you are giving us jigsaw pieces without us having any idea of what the finished picture should look like.

 

[etizzle]

ok,give me a second,

i will create something for you so that you can visualize it,
https://www.dropbox.com/s/2k8hkv2nr615xp8/basically what i am after.xlsx?dl=0

thanks all for your time

 

[AliGW]

OK. Try this (drag the formula in J6 down):

Excel 2016 (Windows) 32 bit

	J	K	L	M	N
DATA MATRIX
Apartment no.	Defects

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J5[/TH]
[TD="align: left"]=C5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J6[/TH]
[TD="align: left"]=INDIRECT("C"&ROWS($J$6:J6)*32+5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[etizzle]

hi Ali
thanks for the code,
it only works for the second data entry form(which is on the left of the spreadsheet)
and not any that i add to it later
i will show you below

https://www.dropbox.com/s/q2i6tsunm...er(after fix,only works for 1 form).xlsx?dl=0

 

[AliGW]

That's because you have implemented it incorrectly.

In J5:

=C5

In J6 (drag copied down):

=INDIRECT("C"&ROWS($J$6:J6)*32+5)

 

[etizzle]

is this what your saying?

https://www.dropbox.com/s/5vgnfapxy... after(is this what you are saying).xlsx?dl=0

?

could you upload a copy of it implamented properly?

Kindest regards,
Ethan