+ Reply to Thread
Results 1 to 4 of 4

Offset a cell using a dynamic (moving) reference cell

  1. #1
    Registered User
    Join Date
    09-01-2010
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Offset a cell using a dynamic (moving) reference cell

    I'm having great difficulty here with something I suspect is very easy to achieve. I've attached a screenshot of my worksheet entitled astrology.

    On row 53, there are 24 divisions of 360. At any single time, 23 of the cells will return false and 1 will be true. The cell that is true changes from day to day depending upon the position of the moon.

    What I need is to place the number in row 38 that is in the same column as where the true (1) is located on row 53, into cell A40.

    So in this example, the true (1) on row 53 is in column D, which correlates with the number 30 on row 38 on column D.

    I cannot use an if function because there are more than 7 ifs... as there are 24 divisions of 15 degrees within the 360 circle.

    I dont know how to use offset function as the true cell changes every day, so therefore I cannot use offset -15 rows as there is no fixed cell that I can reference from as the true cell could be anyone of B53 - Y53.

    I'm pulling what hair I have left over this and will be most grateful if anybody can show me the way forward.
    Last edited by wavelet; 09-01-2010 at 09:13 AM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Simple function problem help needed

    try in A40

    =index($B$38:$Y$38,match(1,$B$53:$Y$53,0))

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Offset a cell using a dynamic (moving) reference cell

    this is the best I can do off the screen shot. If that does not help, post a real Excel file.

  4. #4
    Registered User
    Join Date
    09-01-2010
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Offset a cell using a dynamic (moving) reference cell

    Quote Originally Posted by teylyn View Post
    this is the best I can do off the screen shot. If that does not help, post a real Excel file.
    You best is good enough, thankyou very much. I'll now study your code so I can understand the functions involved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1