Next Value Ignoring Blanks

  • Thread starter Thread starter Alec Kolundzic
  • Start date Start date
A

Alec Kolundzic

Is there a function that will take the next sequential
value in a column, ignoring blanks.

For instance, if B10 to B15 has the values 10 to 15 then
the value in B20 should be 16.

Thanks
Alec
 
Hi Alec,

You could try this formula in B20

=INDIRECT(CHAR(COLUMN($B$10)+64)&MAX(ROW($B$10:OFFSET(B20,-1,0))*(NOT(ISBLAN
K($B$10:OFFSET(B20,-1,0))))))+1

It is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Alec,

Unclear what you want to do, but perhaps(?) in B20, use the formula

=MAX($B$1:B19)+1

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie

However, I over simplified the question, what I'm really
after is a function that will enter the next sequential
entry into B17 as shown below:-

B10 = 1ETD07GH001-C03
B11 = 1ETD07GH001-C04
B12 = 1ETD07GH001-C05
B13 = 1ETD07GH001-C06
B14 = 1ETD07GH001-C07
B15 =
B16 =
B17 = 1ETD07GH001-C08
 
Thanks Bob

However, I over simplified the question, what I'm really
after is a function that will enter the next sequential
entry into B17 as shown below:-

B10 = 1ETD07GH001-C03
B11 = 1ETD07GH001-C04
B12 = 1ETD07GH001-C05
B13 = 1ETD07GH001-C06
B14 = 1ETD07GH001-C07
B15 =
B16 =
B17 = 1ETD07GH001-C08

Your function works on numbers only
 
Alec,

In B17, use the array formula (entered with Ctrl-Shift-Enter)

="1ETD07GH001-C" &
TEXT(MAX(IF(LEN(B$10:B16)=15,(RIGHT(B$10:B16,2)*1),0))+1,"00")

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie

It works a treat.



-----Original Message-----
Alec,

In B17, use the array formula (entered with Ctrl-Shift- Enter)

="1ETD07GH001-C" &
TEXT(MAX(IF(LEN(B$10:B16)=15,(RIGHT(B$10:B16,2)*1),0)) +1,"00")

HTH,
Bernie
MS Excel MVP




.
Click to expand...
 
Alec,

You're quite welcome. Glad to hear it worked out.

HTH,
Bernie
MS Excel MVP
 
Back
Top