display specific next highest value from a column containing multi

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

GarysStudent
Sat May 10 11:17:03 CDT 2008

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

T
Sat May 10 11:23:49 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> 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

bvasquez
Sat May 10 11:46:00 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> > 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
>
>
>

RagDyeR
Sat May 10 12:01:54 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
news:B8086EA0-2A61-432C-A347-2E8EFB429D05@microsoft.com...
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" <bvasquez@discussions.microsoft.com> wrote in message
> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> > 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
>
>
>

RagDyeR
Sat May 10 12:07:14 CDT 2008

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" <ragdyer@cutoutmsn.com> wrote in message
news:O2lFO%23rsIHA.552@TK2MSFTNGP06.phx.gbl...
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" <bvasquez@discussions.microsoft.com> wrote in message
news:B8086EA0-2A61-432C-A347-2E8EFB429D05@microsoft.com...
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" <bvasquez@discussions.microsoft.com> wrote in message
> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> > 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
>
>
>

T
Sat May 10 12:10:24 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
news:B8086EA0-2A61-432C-A347-2E8EFB429D05@microsoft.com...
> 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" <bvasquez@discussions.microsoft.com> wrote in message
>> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
>> > 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
>>
>>
>>

RagDyeR
Sat May 10 12:11:36 CDT 2008

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" <ragdyer@cutoutmsn.com> wrote in message
news:uEtGNBssIHA.1952@TK2MSFTNGP05.phx.gbl...
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" <ragdyer@cutoutmsn.com> wrote in message
news:O2lFO%23rsIHA.552@TK2MSFTNGP06.phx.gbl...
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" <bvasquez@discussions.microsoft.com> wrote in message
news:B8086EA0-2A61-432C-A347-2E8EFB429D05@microsoft.com...
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" <bvasquez@discussions.microsoft.com> wrote in message
> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> > 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
>
>
>

bvasquez
Sat May 10 12:31:01 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
> news:B8086EA0-2A61-432C-A347-2E8EFB429D05@microsoft.com...
> > 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" <bvasquez@discussions.microsoft.com> wrote in message
> >> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>
>

Ron
Sat May 10 12:45:41 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> 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

T
Sat May 10 12:53:09 CDT 2008

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" <ragdyer@cutoutmsn.com> wrote in message
news:un1PpDssIHA.5096@TK2MSFTNGP02.phx.gbl...
> 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" <ragdyer@cutoutmsn.com> wrote in message
> news:uEtGNBssIHA.1952@TK2MSFTNGP05.phx.gbl...
> 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" <ragdyer@cutoutmsn.com> wrote in message
> news:O2lFO%23rsIHA.552@TK2MSFTNGP06.phx.gbl...
> 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" <bvasquez@discussions.microsoft.com> wrote in message
> news:B8086EA0-2A61-432C-A347-2E8EFB429D05@microsoft.com...
> 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" <bvasquez@discussions.microsoft.com> wrote in message
>> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
>> > 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
>>
>>
>>
>
>
>
>

bvasquez
Sat May 10 13:04:00 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
> > 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
>
>
>

T
Sat May 10 13:12:39 CDT 2008

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" <bvasquez@discussions.microsoft.com> wrote in message
news:9030C2EA-7BD1-4F16-AE35-08003522A6EB@microsoft.com...
> 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" <bvasquez@discussions.microsoft.com> wrote in message
>> news:B8086EA0-2A61-432C-A347-2E8EFB429D05@microsoft.com...
>> > 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" <bvasquez@discussions.microsoft.com> wrote in message
>> >> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

Ron
Sat May 10 15:38:24 CDT 2008

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

Regards,

Ron
Microsoft MVP (Excel)


"bvasquez" <bvasquez@discussions.microsoft.com> wrote in message
news:E97856B2-631E-4983-8161-7F9AE03414E7@microsoft.com...
> 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" <bvasquez@discussions.microsoft.com> wrote in message
>> news:308DDF5F-B821-4E53-B24B-71215FA33EE8@microsoft.com...
>> > 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
>>
>>
>>