Need formula to find the next value in series by comparing values of adjacent cells

Hi Friends,
I have a file attached with this post in which I want to insert a formula that it should automatically pick only that next value from the given series if its adjacent cells are empty. please have a look at a snap shot or the excel file attached, you can find more crisp and clear explanation for desirous resolution.

Thanks in advance to all the readers and solvers.

snap shot.png

Try this:

In cell E2 , put this function:

Formula:

Please Login or Register  to view this content.

In column G2 put this function:

Formula:

Please Login or Register  to view this content.

Hi warriorpoet7176 ,

Please try this formula

=INDEX(A2:A17,IF(COUNTA($B$2:$B$17)+1=COUNTA(C2:C17)+1,COUNTA($B$2:$B$17)+1,COUNTA($B$2:$B$17)))

Regards,

Hi Blueblink01,
Thanks for this formula that worked great, if possible can you please explain how you derive this formula and how does it works.

Originally Posted by blueblink01

Hi warriorpoet7176 ,

Please try this formula

=INDEX(A2:A17,IF(COUNTA($B$2:$B$17)+1=COUNTA(C2:C17)+1,COUNTA($B$2:$B$17)+1,COUNTA($B$2:$B$17)))

Regards,

Originally Posted by JDoe314

Try this:

In cell E2 , put this function:

Formula:

Please Login or Register  to view this content.

In column G2 put this function:

Formula:

Please Login or Register  to view this content.

Hi JDoe,
Thank You!!
your idea really worked but I wanted it to get this problem resolve in one cell, here I need two cells to get single result.

Still appreciate your effort & am very grateful to you

Hi Blueblink01,
The original file is attached with this post I request you to kindly get this formula working on this sheet as well..

File : Copy of Bll Book.xlsm

Originally Posted by warriorpoet7176

Hi Blueblink01,
Thanks for this formula that worked great, if possible can you please explain how you derive this formula and how does it works.

HI Warriorpoet7176,

I would like to ask why the file is saved as .xlsm? I guess Bill No in sheet 2,cell A2, will reflect "Codes" in sheet "Customer records"

Please try this formula

=INDEX('Customer Records'!$B$2:$B$23,IF(COUNTA('Customer Records'!$C$2:$C$23)+1=COUNTA('Customer Records'!$D$2:$D$23)+1,COUNTA('Customer Records'!$C$2:$C$23)+1,COUNTA('Customer Records'!$C$2:$C$23)))

Thanks and Regards,