How to write a formula

[henry]

Hi,
I have a column that lists the names of pieces of equipment. These are not sorted in any order in this column (the sheet is sorted by the owner's name in another column). It includes, for example
A5
C1
A3
B2
A4
A1
C3
etc
Further down in this column I have the next available number for A, B & C. To date I have been manually updating this but I am sure there must be a way to write a formula that searches the column above and gives me the next number. Any suggestions?

Cheers

Henry

I have now uploaded a smaple version of the spreadsheet. The real one has about 20 columns. You can see my list of varous equipment numbers in column 2 then at the end of column 2 the list that I am currently manually updating of the next number to use. Hope this is clearer

[Biff]

Hi!

Your post isn't real clear about what what you want!

I'm assuming the "next available number" is the next number in sequence for
a particular letter.

So, based on my understanding and the posted sample data the next available
number for letter A would be 6.

Assume you have 3 cells:

A100 = A
A101 = B
A102 = C

Formula in B100 entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX((LEFT(A$1:A$50)=A100)*(MID(A$1:A$50,2,255)))+1

Copy down to B102

Biff

"henry" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a column that lists the names of pieces of equipment. These are
> not sorted in any order in this column (the sheet is sorted by the
> owner's name in another column). It includes, for example
> A5
> C1
> A3
> B2
> A4
> A1
> C3
> etc
> Further down in this column I have the next available number for A, B &
> C. To date I have been manually updating this but I am sure there must
> be a way to write a formula that searches the column above and gives me
> the next number. Any suggestions?
>
> Cheers
>
> Henry
>
>
> --
> henry
> ------------------------------------------------------------------------
> henry's Profile:
> http://www.excelforum.com/member.php...o&userid=23872
> View this thread: http://www.excelforum.com/showthread...hreadid=518523
>

[Biff]

Ok, looks like I had the right idea. Now let's tweak it for the specific
application:

These formulas need to be array entered.

Formula for cell B45:

="WKS"&TEXT(MAX((LEFT(B$3:B$42,3)="WKS")*(MID(B$3:B$42,4,255)))+1,"0#")

Formula for cell B46:

="LAB"&TEXT(MAX((LEFT(B$3:B$42,3)="LAB")*(MID(B$3:B$42,4,255)))+1,"0#")

Formula for cell B47:

="LAP"&TEXT(MAX((LEFT(B$3:B$42,3)="LAP")*(MID(B$3:B$42,4,255)))+1,"0#")

Biff

"henry" <[email protected]> wrote in
message news:[email protected]...
>
> I've attached an abbreviated version of the worksheet showing just 2
> columns. There are about 20 columns in the whole workbook. As you can
> see if you scroll to the end of the second column, I have the next
> numbers waiting to be assigned that I am so far managing manually. I
> hope this makes it clearer
>
>
> --
> henry
> ------------------------------------------------------------------------
> henry's Profile:
> http://www.excelforum.com/member.php...o&userid=23872
> View this thread: http://www.excelforum.com/showthread...hreadid=518523
>

[henry]

Thans very much for that. IY works exactly how I'd like it to work. May I push my luck and ask you to explain the formula so that I can use it for other worksheets?
B$3:B$42,3)="WKS")*(MID(B$3: B$42,4,255)))+1,"0#")
What is the "3" and the "4" and the "255" representing?

[Biff]

> LEFT(B$3:B$42,3)="WKS")*(MID(B$3: B$42,4,255)))+1,"0#")
> What is the "3" and the "4" and the "255" representing?

3 refers to: if the first 3 characters from the left in the cells in the
range equal WKS, then TRUE, if not FALSE.

B3 = WKS18, so, LEFT(B$3,3)="WKS" = TRUE

4 and 255 refers to: find the numeric value in the cells. The numeric values
start in the 4th position and may be of an unknown length so to make sure we
find the entire number we use an arbitrarily large number like 255 which
means start at character 4 and extract the next 255 characters. Chances are
pretty good that you're not using a number that is more than 255 characters
long so this ensures we extract the entire number.

B3 = WKS18, so, MID(B$3,4,255) = 18

Then:

TRUE*18 = 18

18 is then passed to the Max function. If 18 is the max value then 1 is
added to it to give you:

WKS19

Which would be your next available number.

The "0#" is a formatting code that allows for leading zeros where needed.

Biff

"henry" <[email protected]> wrote in
message news:[email protected]...
>
> Thans very much for that. IY works exactly how I'd like it to work. May
> I push my luck and ask you to explain the formula so that I can use it
> for other worksheets?
> B$3:B$42,3)="WKS")*(MID(B$3: B$42,4,255)))+1,"0#")
> What is the "3" and the "4" and the "255" representing?
>
>
> --
> henry
> ------------------------------------------------------------------------
> henry's Profile:
> http://www.excelforum.com/member.php...o&userid=23872
> View this thread: http://www.excelforum.com/showthread...hreadid=518523
>

[henry]

Thankyou, I really appreciate you taking the time to explain this so I can do more than just utilise it this time, but understand what to do next time.

Cheers

Henry

[Biff]

You're welcome. Thanks for the feedback!

Biff

"henry" <[email protected]> wrote in
message news:[email protected]...
>
> Thankyou, I really appreciate you taking the time to explain this so I
> can do more than just utilise it this time, but understand what to do
> next time.
>
> Cheers
>
> Henry
>
>
> --
> henry
> ------------------------------------------------------------------------
> henry's Profile:
> http://www.excelforum.com/member.php...o&userid=23872
> View this thread: http://www.excelforum.com/showthread...hreadid=518523
>