Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofill and links (Excel 2000)

    Hi Everyone,

    Is there a quick way to copy linked cell reference.
    For example I am referencing dw200 from in another workbook and I want to use the autofill
    and copy it across a range of cells. So it will be dx200, and dy200 etc. However when I
    use the autofill option it it just copying the same reference - and then I have to manually go in
    and edit each cell reference.

    Is there an easy way to do this.

    Thanks Kindly

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    How are you doing the autofill? Is the DW200 in a formula? If it is not in a formula, then it will be treated as text, not as a cell reference and will not be updated. If it is in a formula, then you should be able to enter the formula into a cell and then drag the fill handle in the lower right corner right and have the reference updated. Is that not happening?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Hi Legare Coleman,

    The reference looks like this

    'C:FINANCEBANKCashflow[Dcshbk16.xls]daily'!DU$200

    Is this correct? And when I use the autofill is copies the same thing across.

    Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    What are you trying to do? That is just a text string, and will not be updated when it is autofilled, and it should just display as the text not the value of the cell in the other workbook. If you want that to actually get the value from that cell in the other workbook, and be updated when you autofill it, then you will need to place an equal sign in front of it like this:

    <pre>='C:FINANCEBANKCashflow[Dcshbk16.xls]daily'!DU$200
    </pre>

    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Sorry it does have an equal sign, and it does display the value from the other file and cell reference.

    However, what I am trying to do it find an easy way to copy this across a series of cells (dragging and use
    autofill). When I use autofill in the usual way it, changes the cell reference relative to where i am copying
    it.
    This is not it is just autofilling the next cell with the same file(which is what i want) and the same cell
    reference(DU200- which is not what I want) I want it to be DV200, then DW200 etc.

    Hope that makes more sense.

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    That is what it should be doing. Would it be possible to upload the file so we can take a look?
    Legare Coleman

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Autofill and links (Excel 2000)

    Hi Kerrie,

    Normally when you insert a link to an external spreadsheet, Excel gives you an absolute cell reference
    (eg =C:FINANCEBANKCashflow[Dcshbk16.xls]daily'!$DU$200).

    The formula in your post doesn't have the '$' immediately before DU, so the formula links to a relative column (DU) and an absolute row ($200).

    If you use autofill to copy the formula down, the row number won't change unless you first remove the '$' sign. Conversely, if you're using autofill to copy the formula across, the column references will change unless you prefix the DU with a '$' sign.

    Autofilling across doesn't change the referenced rows and autofilling down doesn't change the referenced columns.

    Hope this helps
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Hi Legare Coleman,

    The autofill works now when I copy the reference across. Yippee!!
    However, do you know a way to:

    1-May-02 2-May-02 3-May-02 6-May-02
    F'cast Actual F'cast Actual F'cast Actual F'cast Actual
    0 1 2 3 4 5 0
    0 0 0 0
    80 150 323 0 0
    34 0 0 0
    0 0 0 0
    0 0 0 0
    0 0 0 0
    0 0 0 0
    0 38 0 0
    0 315 0 0
    0 114 151 678 3 4 5 0

    If I have columns as above: f'cast and Actual
    and I want to only use the autofill(linking to another worksheet) on the Actual columns and thus increase the cell reference by one only, so basically I want to use the autofill on every second column.

    Is there an easy way, or do I still have to manually go in and edit the reference?

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Place 5/1/02 in cell A1. Now select cells A1 and A2 and fill them to the right for as far as you need to go.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Hi Legare Coleman,

    Thank you for you previous help [img]/forums/images/smilies/smile.gif[/img]
    Do you know any way if I have a worksheet and heading similiar to below:

    May June July
    Forecast Actual Forecast Actual Forecast Actual

    And so.
    I am linking the "Actual value" to another worksheet. I am using the autofill feature to copy across the
    formula and it is working perfectly, however I would like to

    Copy the formula only to the "Actual" columns.
    "Forecast" to remain blank.
    when using autofill or any other function, that the reference in the actual column only to increase by one
    At the moment it is increasing by 2 because of the forecast column.

    Hope this makes sense.

    Thanks Kindly,

    Kerrie

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Select both cells (the one with the formula/value and the empty one next to it) and fill both at the same time.
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Hi Legare Coleman,


    It doesn't place anything in the "Forecast" column but It still increase the reference by 2, because I am copying it across 2 columns.

    So it looks similiar to:

    Forecast Actual Forecast Actual Forecast Actual
    B$76 D$76 F$76

    I have put cell references instead of values.

    I want it too look like:

    Forecast Actual Forecast Actual Forecast Actual
    B$76 C$76 D$76

    Thanks Kindly,


    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Autofill and links (Excel 2000)

    Hi Kerrie,

    The following formula should work:
    =OFFSET(Sheet1!$A$1,,INT(COLUMN()/2))
    where:
    . the first target cell is in column A or B;
    . Sheet1! is the name of your source worksheet (eg 'C:FINANCEBANKCashflow[Dcshbk16.xls]daily'! per an earlier post); and
    . $A1 is the first cell in the range on your source worksheet you want to link to (in this case it looks like that would be $B76).

    If the first target cell is not in column A or B, use
    =OFFSET(Sheet1!$A$1,,INT(COLUMN()/2)-n)
    where:
    . n is the number of columns in your target worksheet to the left of the first target cell.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill and links (Excel 2000)

    Hello,

    I tried you suggestion and it worked [img]/forums/images/smilies/smile.gif[/img] - what a useful function.

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •