# Find the maximum occurrence across sheets

This is kinda difficult to explain...

Is it possible to let Google Sheets tell me what the maximum value is from some chosen columns in MORE THAN ONE sheet (in the same document)?

I namely need to find the largest occurrence of an id.

To clarify:

I would like to have a column that contains unique row ids. But I have different sheets in the document. So when I add a new row in one of the sheets, I would like to add the lowest id available (that is, an id one step larger than the currently maximum id).

But how can I find the next possible id in the sequence of ids that are already in use?

Now, instead of me having to look through all sheets, I would like to be able to find the next value for the cell automatically. E.g. by having it written in a cell in the corner of the sheet, or whatever. Then I can insert in manually. I just want to avoid LOOKING for it manually. Can that be done?

A bonus feature would of course be, if I can give the ids a prefix - e.g. `R134` instead of just `134`. This means that a perfect solution of course would be, if the ids don't have to be all numeric.

• Do you expect to have to delete rows at any time? That would make the solution more complex. – Tom Horwood Oct 6 '13 at 1:32
• @TomHorwood Hmm... Changes might happen, yes. E.g. the ids in the coloumns are not necessarily sorted descending from top to bottom - they can be mixed. So the solution must be able to search for the largest id. – Steeven Oct 6 '13 at 9:29
• Did this solution work for you? – Jacob Jan Tuinstra Mar 15 '14 at 19:21
• Hi @JacobJanTuinstra, I found another procedure with my id's. No, I didn't solve it, since I stopped working on it. The answer here might be useful, though. – Steeven Mar 15 '14 at 22:35

I've shared a sheet for you to play with here. There is a fourth sheet to help write the second solution

The simple solution (with only numeric ids) is quite straightforward:

``````=MAX(Sheet1!A2:A,Sheet2!A2:A, Sheet3!A2:A)
``````

(This assumes each column has a title)

The situation with a prefix works like this:

``````="R" & MAX(
ARRAYFORMULA(IF(ISBLANK(Sheet1!B2:B), 0,
VALUE(RIGHT(Sheet1!B2:B, LEN(Sheet1!B2:B)-1)))),
ARRAYFORMULA(IF(ISBLANK(Sheet2!B2:B), 0,
VALUE(RIGHT(Sheet2!B2:B, LEN(Sheet2!B2:B)-1)))),
ARRAYFORMULA(IF(ISBLANK(Sheet3!B2:B), 0,
VALUE(RIGHT(Sheet3!B2:B, LEN(Sheet3!B2:B)-1))))
)
``````

What I'm doing here is:

1. Use an arrayformula to repeat a function across each of the cells in a range.
2. Check that cell is not blank as B2:B means every cell from B2 down in the B column
3. If it is blank, assign it 0, which will mean the returned range is guaranteed to have values
4. Otherwise, remove the first character from "R...", then use VALUE to convert it to a number
5. Repeat for each sheet
6. Get the MAX value from the resulting ranges
7. Add R to the front

This assumes the prefix is constant and only one character.