Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, MN
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    I've been digging through the help, and getting in more and more over my head.

    I'm graphing a sliding windows of two weeks worth of data. I always know where my start is. My data - for ease of human usage - is split up in sheets by month (February data in Sheet February, March data in Sheet March, etc.). My graph is based on a range in the Sheet Summary.

    Summary!A2 currently points to February!R1.
    Summary!B2 currently points to February!R2.
    Summary!A3 currently points to February!S1.
    etc.

    I'm currently going in and manually changing 60 cells worth of data every Monday to point to the current week's range (i.e., last week & this week).

    Does anyone know a way to say WITH FORMULAS 'Cell A2 points to the cell just below the cell that Cell A1 points to'? And 'Cell B1 points to the cell just to right of the cell that Cell A1 points to'?

    The Offset function almost pulls it off. If I could figure out a way to get the address of the cell that an arbitrary cell points to, then I think I could use Indirect and Offset to get to what I want.

    Does this make any sense at all?

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    If '...the cell that A1 points to' means, cell A1 contains an address (for example, you type "D1" into cell A1), this will work (that is, you will get the contents of cell D2):

    <pre>=OFFSET(INDIRECT(A1),1,0,1,1)
    </pre>


    If instead you mean, the formula in A1 refers to some other cell (for example, "=D1"), the following will work reliably if the contents of the range are unique:

    <pre>=INDEX(range,MATCH(A1,range,0)+1,1)
    </pre>


    where 'range' is the range containing the potential targets of the formula in cell A1.

    For example if the range is D110, and it contains "One", "Two", "Three", etc., and cell A1 contains "Five", the result of the formula will be "Six".

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Scott

    OK so when you say:

    <font color=blue> Does anyone know a way to say WITH FORMULAS 'Cell A2 points to the cell just below the cell that Cell A1 points to'? And 'Cell B1 points to the cell just to right of the cell that Cell A1 points to'? </font color=blue>

    You mean you want to return the data in two rows, one known and the other is not.

    Look at my attached workbook, and let me know if this would work for you.

    Change the B4 cell to some value and watch the top two rows change data.

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, MN
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    Close, but not quite.

    I'm recording 5 numbers on a daily basis. I'm recording them by month. Each month goes into a new worksheet. I need to graph last week + this week to date. Month end always comes at some point that I need to graph across it.

    If I tell my Summary sheet "go grab data from sheet x & sheet y", I can manually copy the addresses in. I'm trying to come up with a way to enter fill in one cell (if all my data is in one month) or two cells (if my data is in two months).

    I've attached a sample with carefully randomized data, just to show what I'm currently working with.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    (see my attachment)

    I hope this can help you
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    Sorry, the previous file has a small error in formulates of headers date
    Attached Files Attached Files

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, MN
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    It looks like what I want - but I can't figure out how it works!

    What is the DIA.LAB function? I can't find it anywhere. And the moment I change the reference date, all the other columns display "#NAME?".

    OK, I take that back - I think I see how the data lookup is working. And I can easily live with changing from an across format to a down format!

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    Woppp, my Excel is in Spanish and this function could not be translated.

    Function DIA.LAB is NETWORKDAYS in English

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, MN
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Reference Arithmetic (Office 2000 SR-1 / Excel)

    Even after I loaded the Analysis Add-in Pack, my Excel didn't like NETWORKDAYS - but if I use WORKDAY instead, it looks like I'm getting the same answer.

    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
  •