How to write a formula

H

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
 
B

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
 
H

henry

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
 
B

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
 
H

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?
 
B

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?
Click to expand...

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
 
H

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
 
Top