Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can you duplicate relative references in a formula (2003/SP-2)

    My subject is not very descriptive. Sorry.

    We all know how a relative reference updates when you copy or move a formula. So, SUM(A1:A4) becomes SUM(C1:C4) if you paste the formula 2 cells to the right. It's a very useful function.

    I would like to emulate this with some variables. I'm hoping to avoid using VBA on this, but we'll see. To give an example, I want to reference three cells that are to the right of a specific cell. So, I'm using OFFSET, as such:
    =OFFSET(A17, 0, 1)
    =OFFSET(A17, 0, 2)
    =OFFSET(A17, 0, 3)
    =OFFSET(A18, 0, 1)
    =OFFSET(A18, 0, 2)
    =OFFSET(A18, 0, 3)
    and so on...

    I intend to paste this information a lot. I'd rather not have to go in and manually change each OFFSET value. For this example, I could just do the three cells and then Fill Down, but this is a simplified example; I have other formulas where incrementing a variable would be very handy.

    Is there a variable I can plug into Excel formulas that increments in a manner similar to relative references? If worse comes to worst, I could always create a hidden column of 1, 2, 3, 4, 5, etc., and just use =OFFSET(A17, 0, IV1). I'd rather not, but I can work with it.

    Kevin

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can you duplicate relative references in a formula (2003/SP-2)

    Instead of changing the relative start cell, lock it and change the row parameter:. Get the row/column number from the current row of the cell like the example I did in the thread starting at <post:=588,031>post 588,031</post:> .

    To give an example formula will depend on where the cells are going to go. If you can't figure it out from my examples, provide more details and I can be more specific for your setup.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can you duplicate relative references in a formula (2003/SP-2)

    Say the first formula is in P12. You could use this:

    =OFFSET($A$17,ROW()-ROW($P$12)+1,0)

    and fill down as far as needed. The ROW() function returns the row of the cell containing the formula; the row of the start cell (P12) is subtracted, then 1 added, to yield 1 in P12, 2 in P13, etc.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can you duplicate relative references in a formula (2003/SP-2)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> If in P12 I think he would want something more like:
    =OFFSET($A$17,INT((ROW()-ROW($P$12))/3),MOD(ROW()-ROW($P$12),3)+1)

    or just:
    =OFFSET($A$17,INT((ROW()-12)/3),MOD(ROW()-12,3)+1)


    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can you duplicate relative references in a formula (2003/SP-2)

    Oops, I switched rows and columns. Thanks!

Posting Permissions

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