Returning next value from a list
 

I have a spreadsheet where I use ISBLANK to populate a cell when a value is
entered in the cell next to it. I pull the values to populate with from a
list on another sheet. So my formula for cell Sheet1!A2 looks thus:
=IF(ISBLANK(B2),"",Sheet2!A2)

So far so good.

Now the additional requirement has been added to be able to skip one or more
rows when entering values and still get the next value in the list (as
opposed to the value that corresponds to the row number of the entered cell
on the other sheet).

Thanks in advance,

Chris Lucas

Returning next value from a list
 

If I understand correctly...try this:

A2: =IF(ISBLANK(B2),"",INDEX(Sheet2!$A$2:$A$100,COUNTA (B$2:B2)))
Adjust range references to suit your situation and copy that formula down as
needed.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Lucas" wrote:

I have a spreadsheet where I use ISBLANK to populate a cell when a value is
entered in the cell next to it. I pull the values to populate with from a
list on another sheet. So my formula for cell Sheet1!A2 looks thus:
=IF(ISBLANK(B2),"",Sheet2!A2)

So far so good.

Now the additional requirement has been added to be able to skip one or more
rows when entering values and still get the next value in the list (as
opposed to the value that corresponds to the row number of the entered cell
on the other sheet).

Thanks in advance,

Chris Lucas

Returning next value from a list
 

Bravo! Thank you so much. Worked like gangbusters.

CL

"Ron Coderre" wrote:

If I understand correctly...try this:

A2: =IF(ISBLANK(B2),"",INDEX(Sheet2!$A$2:$A$100,COUNTA (B$2:B2)))
Adjust range references to suit your situation and copy that formula down as
needed.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Lucas" wrote:

I have a spreadsheet where I use ISBLANK to populate a cell when a value is
entered in the cell next to it. I pull the values to populate with from a
list on another sheet. So my formula for cell Sheet1!A2 looks thus:
=IF(ISBLANK(B2),"",Sheet2!A2)

So far so good.

Now the additional requirement has been added to be able to skip one or more
rows when entering values and still get the next value in the list (as
opposed to the value that corresponds to the row number of the entered cell
on the other sheet).

Thanks in advance,

Chris Lucas