Links in Excel

[PB]

I am trying to autofill a formula in an excel sheet that links to a different
worksheet.
The formula I am using is
=Orders!AA113
=Orders!AB113
=Orders!AC113
I am manually entering this because when I do an auto fill I get
=Orders!AA113
=Orders! AB114
=Orders!AC115

How do I get the auto fill function to keep the same line number (113)?

 

[Pete_UK]

If you put a $ symbol in front of the 113 then it will not change when
you copy it down:

=Orders!AA$113

However, the AA will not change either, so you will still have to
amend these manually.

Hope this helps.

Pete

 

[Pete_UK]

You could put this formula in your first cell:

=INDIRECT("'Orders'!"&CHAR(INT((ROW(A27)-1)/26)+64)&CHAR(MOD(ROW
(A27)-1,26)+65)&"113")

and then when you copy this down it will give you the equivalent of:

='Orders'!AA113
='Orders'!AB113
='Orders'!AC113
='Orders'!AD113
='Orders'!AE113
='Orders'!AF113

and so on.

Hope this helps.

Pete

 

[CellShocked]

I am trying to autofill a formula in an excel sheet that links to a different
worksheet.
The formula I am using is
=Orders!AA113
=Orders!AB113
=Orders!AC113
I am manually entering this because when I do an auto fill I get
=Orders!AA113
=Orders! AB114
=Orders!AC115

How do I get the auto fill function to keep the same line number (113)?

You can make five cell columns. One with =Orders! all the way down.

One with AA, and then down thru AB AC AD etc.

And one filled with only 113

Then a final cell column that you fill with

Using cell columns E, F, & G respectively for the data strings
mentioned above:

=Concatenate(E1,F1,G1)
Then that one you can drag down.

Then you cut and paste special, values only into your final
(original)target column, from the formula results column.

Once you are finished, you can delete the construct columns you used if
desired.

OR, you could make ONE column (say E) with "Orders!AA" in it, and one
cell lower put the AB suffix, and array drag those down. It should
properly increment.

Then, you can make a concatenate column that goes:

CONCATENATE(E1,"113") and gets arrayed down...

then copy and paste special, values only on that one into your primary
target column.

 

[CellShocked]

Far out.

You could put this formula in your first cell:

=INDIRECT("'Orders'!"&CHAR(INT((ROW(A27)-1)/26)+64)&CHAR(MOD(ROW
(A27)-1,26)+65)&"113")

and then when you copy this down it will give you the equivalent of:

='Orders'!AA113
='Orders'!AB113
='Orders'!AC113
='Orders'!AD113
='Orders'!AE113
='Orders'!AF113

and so on.

Hope this helps.

Pete