I am looking for a cell formula that will look for the largest number in
column "A" in another sheet and return the next number in the sequence.

=MAX(Sheet2!A:A)+1

--
Gary''s Student - gsnu200777

Any suggestions on how to make this work when your cells have text?

At the top of my spreadsheet I have a cell that indicates the next number to
be used. This is because the entries are sorted by a field other than this
identifier, and new entries are added after a row has been inserted in the
correct place. Therefore, the cell at the top is important to know what next
number can be used.

The format for the "number" is C00001, C00002, C00003, ect. the =MAX(A:A)+1
solution does not work for me because the prompt inidactes that it will
ignore text.

Thanks!

"Gary''s Student" wrote:

=MAX(Sheet2!A:A)+1

--
Gary''s Student - gsnu200777

I would use similar but different approach. Remove the C and leading
zeros from the existing ID numbers, leaving only the real numbers.

Then highlight the entire range and apply a custom number format of:

C00000

That will cause 35 to appear as C00035...but the raw number of 35 is
still visible.

Then in the "next number" box at the top, just let is show the Raw
number to type next...36, not C00036. When they type in 36, it will
appear as C00036.

Now your MAX formula will work.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56427