Inline Outline Underline Overline Autocomplete

[Mike Reutter]

I need to know if it is possible and if so how to autocomplete an non linear cell reference. For example take the formula:

=time_collector!AQ2

Increase by 4 cells for every 1 cell dragged. Ultimately looking like this:

=time_collector!AQ2
=time_collector!AQ6
=time_collector!AQ10
=time_collector!AQ14

Normally I would take the first two entires of the reference, select them and drag downward and links would be perfect. But since excel does not seem to like the pattern I cannot figure out how to link them using auto-complete.

My two options are: Ask the experts (You Guys/Girls) or manual entry... resluting in 272 manual links.. ugh

Any help is useful,
ray: I bow to the excel godz ray:

Thanks
Mike

 

[Norie]

Where are these formula going to be located?

If the started say in row 1 of a column you could use an INDIRECT formula like this:

=INDIRECT("time_collector!AQ" &((ROW()-1) *4)+2)

When copied down the number for the cell reference should be incremented as you want.

 

[Mike Reutter]

I dont understand this one, it doesnt seem to work for me. I need the values to show up in the cell that the link is entered in. Which var determines the increment "*4" or "+2"?

 

[Norie]

Well the worksheet function ROW() should return the current row.

So if in A1 you had

=INDIRECT("time_collector!AQ" &((ROW()-1) *4)+2)

the ROW() would return 1, then (ROW()-1) *4)+2 would return 4 and the INDIRECT should point at time_collector!AQ4.

If you need the formula in a cell(s) other than A1 I think more information might be needed.

 

[Mike Reutter]

Im still confused, I dont think that this will work for my specific application.

Thanks for your help though.

 

[Norie]

Mike

Can you supply more information?

What I posted was based on your original post.

 

[Mike Reutter]

I am just going to use replace and that should reduce the amount of time.

Thanks for all your help.

||THREAD CLOSED||