Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Links in Excel
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)? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Links in Excel
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 On Jan 21, 11:32*pm, PB wrote: 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)? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Links in Excel
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 On Jan 21, 11:32*pm, PB wrote: 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)? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Links in Excel
On Thu, 21 Jan 2010 15:32:01 -0800, PB
wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Links in Excel
Far out.
On Thu, 21 Jan 2010 16:52:52 -0800 (PST), Pete_UK wrote: 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 On Jan 21, 11:32*pm, PB wrote: 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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow opening links between workbooks with links created in 2003 | Excel Discussion (Misc queries) | |||
Update links box gives Continue or Edit Links dialog | Excel Discussion (Misc queries) | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) | |||
EXCEL - LINKS cannot easily get list of all links & names in book | Excel Worksheet Functions | |||
can't update links...can't find links | Excel Discussion (Misc queries) |