Dragging/ Auto Calculation Problem

[Matt G]

Hi all-

Issue:

I am dragging down rows to auto calculate
Problem: It increments in fours

Is there a feature that turns this off?


Specifics: Sheet2 is four rows of calculations linked from data in on
line on Sheet1.
(Recorded a Macro to capture this action)

So Row 2, Sheet1 is the origin of all my entries on Rows 1,2,3,4 o
Sheet2

And Row 3, Sheet1 will be the origin of all my entries on Row
5,6,7,8........Row 4, Sheet1 will be the origin of entries on Row
9,10,11,12 on Sheet2 etc


What is Occurring: When I drag boxes on sheet2 to auto complete i
starts with Row 6, Sheet1 as the reference, and then Row 10 for th
next and Row 14 for the next.

I need it to not skip every four but instead go in numerical order Ro
2, 3, 4, 5 Sheet1 etc.

Any suggestions?

Matt S

 

[Bernie Deitrick]

Matt SF,

You can use INDIRECT and some logic to build your formulas.

After you've finished, you can combine all these formulas into one long
formula (if you have logic for the second part), but to start, you can do
something like this.

In B1, enter the formula

=INT((ROW()-1)/4)+2
This will return four 2s, then four 3s, etc.

In C1:C4, enter the column numbers from which you want to retreive the
value, where A =1, B = 2, etc. This pattern should be repeated. So, for
instance, C1 could have a 2 for B, C2 a 4 for D, C3 a 7 for G, and C4 an 11
for K. So the pattern down column C would be 2,4,7,11,2,4,7,11,2,4,7....

In D1, use the formula:

=INDIRECT("Sheet1!"&ADDRESS(B1,C1))

and copy down as far as you need.

HTH,
Bernie
MS Excel MVP

 

[Matt G]

Hey Bernie,

Just got in and looked over your reply. Looks very promissing
something I nevr thought of doing. I appreciate your time and thought
on the problem. I will post later and let you know how it works out.

Mat