copy formulas with fill handle
in [Functions]
|
Prev: to lookup data for 'today()'
Next: Rows to table
From: Bob L on 30 Mar 2010 09:03 I have two spreadsheets and want to link info from one sheet to another. I have totals at the bottom of columns and I want to be able to put those totals into another spreadsheet. I want to do this with the fill handle instead of entering one by one. I have totals in lets say B66,C66,D66 and so on. I want to be able to put those numbers in the next spreadsheet in cells B11,B12,B13 and so on. B11 should equal B66 , B12 should equal C66 and so on. Any help would be appreciated.
From: Bernard Liengme on 30 Mar 2010 10:24 In B11 of Sheet2 enter =OFFSET(Sheet1!$B$66,0,ROW(A1)-1) This will evaluate to =OFFSET(Sheet1!$B$66,0,0) and will be the same as =Sheet1!B66 Drag the formula down the column In B12 you will have =OFFSET(Sheet1!$BA$661,0,ROW(A2)-1) This will evaluate to =OFFSET(Sheet1!$B$66,0,1) and will be the same as =Sheet1!C66 etc best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Bob L" <rlabonte(a)myfairpoint.net> wrote in message news:BB9E88A6-B3B6-465B-870D-3F31E4687640(a)microsoft.com... > I have two spreadsheets and want to link info from one sheet to another. > I have totals at the bottom of columns and I want to be able to put those > totals into another spreadsheet. I want to do this with the fill handle > instead of entering one by one. > > I have totals in lets say B66,C66,D66 and so on. I want to be able to put > those numbers in the next spreadsheet in cells B11,B12,B13 and so on. B11 > should equal B66 , B12 should equal C66 and so on. > > Any help would be appreciated.
From: Bob L on 30 Mar 2010 11:06 That worked great. Thanks "Bernard Liengme" <bliengme(a)TRUENORTH.stfx.ca> wrote in message news:uhbqlSB0KHA.2436(a)TK2MSFTNGP04.phx.gbl... > In B11 of Sheet2 enter =OFFSET(Sheet1!$B$66,0,ROW(A1)-1) > This will evaluate to =OFFSET(Sheet1!$B$66,0,0) and will be the same as > =Sheet1!B66 > Drag the formula down the column > In B12 you will have =OFFSET(Sheet1!$BA$661,0,ROW(A2)-1) > This will evaluate to =OFFSET(Sheet1!$B$66,0,1) and will be the same as > =Sheet1!C66 > etc > best wishes > -- > Bernard Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > > > "Bob L" <rlabonte(a)myfairpoint.net> wrote in message > news:BB9E88A6-B3B6-465B-870D-3F31E4687640(a)microsoft.com... >> I have two spreadsheets and want to link info from one sheet to another. >> I have totals at the bottom of columns and I want to be able to put those >> totals into another spreadsheet. I want to do this with the fill handle >> instead of entering one by one. >> >> I have totals in lets say B66,C66,D66 and so on. I want to be able to >> put those numbers in the next spreadsheet in cells B11,B12,B13 and so on. >> B11 should equal B66 , B12 should equal C66 and so on. >> >> Any help would be appreciated. >
From: T. Valko on 30 Mar 2010 12:38 Another one... Entered in Sheet1 B11 and copied down as needed... =INDEX(Sheet2!B$66:J$66,ROWS(B$11:B11)) Adjust for the correct end of range J$66. -- Biff Microsoft Excel MVP "Bob L" <rlabonte(a)myfairpoint.net> wrote in message news:BB9E88A6-B3B6-465B-870D-3F31E4687640(a)microsoft.com... >I have two spreadsheets and want to link info from one sheet to another. I >have totals at the bottom of columns and I want to be able to put those >totals into another spreadsheet. I want to do this with the fill handle >instead of entering one by one. > > I have totals in lets say B66,C66,D66 and so on. I want to be able to put > those numbers in the next spreadsheet in cells B11,B12,B13 and so on. B11 > should equal B66 , B12 should equal C66 and so on. > > Any help would be appreciated.
From: bob on 31 Mar 2010 19:18
Thanks Biff, this worked great "T. Valko" <biffinpitt(a)comcast.net> wrote in message news:eOXDndC0KHA.6140(a)TK2MSFTNGP05.phx.gbl... > Another one... > > Entered in Sheet1 B11 and copied down as needed... > > =INDEX(Sheet2!B$66:J$66,ROWS(B$11:B11)) > > Adjust for the correct end of range J$66. > > -- > Biff > Microsoft Excel MVP > > > "Bob L" <rlabonte(a)myfairpoint.net> wrote in message > news:BB9E88A6-B3B6-465B-870D-3F31E4687640(a)microsoft.com... >>I have two spreadsheets and want to link info from one sheet to another. >>I have totals at the bottom of columns and I want to be able to put those >>totals into another spreadsheet. I want to do this with the fill handle >>instead of entering one by one. >> >> I have totals in lets say B66,C66,D66 and so on. I want to be able to >> put those numbers in the next spreadsheet in cells B11,B12,B13 and so on. >> B11 should equal B66 , B12 should equal C66 and so on. >> >> Any help would be appreciated. > > |