Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

To simplify the formula we will cheat and use a "helper column". Say the
data is:

C23
C43
C56
4
5
C77

in G1 enter:
=IF(LEFT(F1,1)="C",--RIGHT(F1,LEN(F1)-1),"") and copy down
we see:

C23 23
C43 43
C56 56
4
5
C77 77

In another cell enter:
=MATCH(LARGE(G:G,2),G:G) this displays 3 ( the row with the second largest)

so LARGE() gets the second largest and MATCH() ids the row where it is.

--
Gary''s Student - gsnu200785


"bvasquez" wrote:

Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I
can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I
can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

Try this *array* formula:

=MAX(IF(LEFT(F10:F100)="C",(--RIGHT(F10:F100,LEN(F10:F100)-1))))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"bvasquez" wrote in message
...
They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I
can
not come up with a formula that will index this column and return the
next
highest C number. F9 needs to display the next highest C number. I can
get
this to work with the standard numbers only (1,2,3,4). Any suggestions

Should mention, this gives the highest "C" number that is presently in the
range.

If you would like, simply append a "+1" to the end of the formula to give
you the *next* number to use.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
Try this *array* formula:

=MAX(IF(LEFT(F10:F100)="C",(--RIGHT(F10:F100,LEN(F10:F100)-1))))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"bvasquez" wrote in message
...
They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I
can
not come up with a formula that will index this column and return the
next
highest C number. F9 needs to display the next highest C number. I can
get
this to work with the standard numbers only (1,2,3,4). Any suggestions

Ok, in other words you want to know what the *last* C number entered is?

1
2
C1
C2
22
4
C3
7
26

=LOOKUP(2,1/(LEFT(F10:F100)="C"),F10:F100)

Result = C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any
numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C
count
climbs consecutively without having to physically look through the range
for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4).
I
can
not come up with a formula that will index this column and return the
next
highest C number. F9 needs to display the next highest C number. I can
get
this to work with the standard numbers only (1,2,3,4). Any suggestions

Meant to post this example (also minus extra parens):

=MAX(IF(LEFT(F10:F100)="C",--RIGHT(F10:F100,LEN(F10:F100)-1)))+1
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
Should mention, this gives the highest "C" number that is presently in the
range.

If you would like, simply append a "+1" to the end of the formula to give
you the *next* number to use.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
Try this *array* formula:

=MAX(IF(LEFT(F10:F100)="C",(--RIGHT(F10:F100,LEN(F10:F100)-1))))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"bvasquez" wrote in message
...
They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I
can
not come up with a formula that will index this column and return the
next
highest C number. F9 needs to display the next highest C number. I can
get
this to work with the standard numbers only (1,2,3,4). Any suggestions

In the following scenario you provided I would like the result in F9 to = C4

"T. Valko" wrote:

Ok, in other words you want to know what the *last* C number entered is?

1
2
C1
C2
22
4
C3
7
26

=LOOKUP(2,1/(LEFT(F10:F100)="C"),F10:F100)

Result = C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any
numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C
count
climbs consecutively without having to physically look through the range
for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4).
I
can
not come up with a formula that will index this column and return the
next
highest C number. F9 needs to display the next highest C number. I can
get
this to work with the standard numbers only (1,2,3,4). Any suggestions

If the C-Values can be out of order,
this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER) returns the largest C-Value + 1:
F9: ="C"&(MAX(IF(LEFT(F10:F100,1)="C",--MID(F10:F100,2,99)))+1)

However, if the C-Values are ALWAYS in ascending order,
this regular formula returns the LAST C-Value + 1:
="C"&(MID(LOOKUP(2,1/(LEFT(F10:F100,1)="C"),F10:F100),2,99)+1)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I
can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

Assuming there are only numbers and "C numbers" in the range (empty cells
and formulas blanks are OK).

A few keystrokes shorter:

=MAX((LEFT(F10:F100)="C")*REPLACE(F10:F100,1,1,0))

--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
Meant to post this example (also minus extra parens):

=MAX(IF(LEFT(F10:F100)="C",--RIGHT(F10:F100,LEN(F10:F100)-1)))+1
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
Should mention, this gives the highest "C" number that is presently in the
range.

If you would like, simply append a "+1" to the end of the formula to give
you the *next* number to use.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
Try this *array* formula:

=MAX(IF(LEFT(F10:F100)="C",(--RIGHT(F10:F100,LEN(F10:F100)-1))))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"bvasquez" wrote in message
...
They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any
numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C
count
climbs consecutively without having to physically look through the range
for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4).
I
can
not come up with a formula that will index this column and return the
next
highest C number. F9 needs to display the next highest C number. I can
get
this to work with the standard numbers only (1,2,3,4). Any suggestions

C-Values can be out of order
F9: ="C"&(MAX(IF(LEFT(F10:F100,1)="C",--MID(F10:F100,2,99)))+1)
That is exactly what I am looking for. Thank you Mr. Coderre. Thank you all
for all your submittals and suggestions. By studying all submitted I have
learned an incredible amount. First time submitting....it has been an
excellent experience.

"Ron Coderre" wrote:

If the C-Values can be out of order,
this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER) returns the largest C-Value + 1:
F9: ="C"&(MAX(IF(LEFT(F10:F100,1)="C",--MID(F10:F100,2,99)))+1)

However, if the C-Values are ALWAYS in ascending order,
this regular formula returns the LAST C-Value + 1:
="C"&(MID(LOOKUP(2,1/(LEFT(F10:F100,1)="C"),F10:F100),2,99)+1)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I
can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

Assuming there are only numbers and "C numbers" in the range (empty cells
and formulas blanks are OK).

Try this array formula** :

="C"&MAX((LEFT(F10:F100)="C")*REPLACE(F10:F100,1,1 ,0))+1

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
In the following scenario you provided I would like the result in F9 to =
C4

"T. Valko" wrote:

Ok, in other words you want to know what the *last* C number entered is?

1
2
C1
C2
22
4
C3
7
26

=LOOKUP(2,1/(LEFT(F10:F100)="C"),F10:F100)

Result = C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
They are random. The C numbers I manually enter. For my application I
am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any
numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins
to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9
to
tell
me what my next consecutive C number will be. This way I ensure the C
count
climbs consecutively without having to physically look through the
range
for
the next highest C number and possibly missing a number in between.

"T. Valko" wrote:

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

--
Biff
Microsoft Excel MVP


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like
(C1,C2,C3,C4).
I
can
not come up with a formula that will index this column and return
the
next
highest C number. F9 needs to display the next highest C number. I
can
get
this to work with the standard numbers only (1,2,3,4). Any
suggestions

You're very welcome.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)


"bvasquez" wrote in message
...
C-Values can be out of order
F9: ="C"&(MAX(IF(LEFT(F10:F100,1)="C",--MID(F10:F100,2,99)))+1)
That is exactly what I am looking for. Thank you Mr. Coderre. Thank you
all
for all your submittals and suggestions. By studying all submitted I have
learned an incredible amount. First time submitting....it has been an
excellent experience.

"Ron Coderre" wrote:

If the C-Values can be out of order,
this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER) returns the largest C-Value + 1:
F9: ="C"&(MAX(IF(LEFT(F10:F100,1)="C",--MID(F10:F100,2,99)))+1)

However, if the C-Values are ALWAYS in ascending order,
this regular formula returns the LAST C-Value + 1:
="C"&(MID(LOOKUP(2,1/(LEFT(F10:F100,1)="C"),F10:F100),2,99)+1)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"bvasquez" wrote in message
...
Just cant seem to get it. I am working with data in F10:F100 that
contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4).
I
can
not come up with a formula that will index this column and return the
next
highest C number. F9 needs to display the next highest C number. I can
get
this to work with the standard numbers only (1,2,3,4). Any suggestions