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

1. ## 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

2. ## 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. ## 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

4. ## 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
•