Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select data from a cell if not null loop (2003)

    Hi All,

    I have a workbook with many pages, one is a summary page, And I want a cell (lets say A1) to get the value from a field on another page lets say page2 and cell E1 if E1 is not null if it is null I want it to look right one cell to F1 and use the value here if not null, it F1 is null then G1 etc etc to an unlimited possibilty.

    I cant use a nested IF because there is a limit to 7 nested IF within excel, so I was thinking of some kind of Move Left if Null but I dont know how to do this.

    Many thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

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

    Re: Select data from a cell if not null loop (2003)

    You can use the following array formula (confirm with Ctrl+Shift+Enter instead of just Enter):

    =INDEX(1:1,MIN(IF(NOT(ISBLANK(E1:IV1)),COLUMN(E1:I V1))))

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select data from a cell if not null loop (2003)

    Thanks HANS

    Very confused how does this work. I have tried to get it to work the only time the value is enter into the box is if there is data in the first field E1

    Each month I add a new figure So on month 1 E1 = (lets say) 10 on month 2 F2 = 15 etc and its on going. I want it to read the last one in the line, the one before the blank value. Sorry I did not do very well at explaining this.

    Kevin
    Regards
    Gerbil (AKA Kevin)

  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: Select data from a cell if not null loop (2003)

    If it is not working, are you sure you confirmed with ctrl-shift-enter? if you select the cell in the formula it should look like:
    {=INDEX(1:1,MIN(IF(NOT(ISBLANK(E1:IV1)),COLUMN(E1: IV1))))}

    [Excel will add the Squiggly brackets to indicate an array formula]

    But I am a little confused. Your original request suggests that you want the value to the furthest left in the row. Which Hans gave you a method

    This one suggests you want the one to the furthest right. This can be done with:
    =HLOOKUP(9.99999999999999E+307,E1:IV1,1)

    If you want something different could you elaborate or perhaps give a sample workbook indicating the setup and the values you want?

    Steve

Posting Permissions

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