Serach for the highest value with text prefix in the whole sheet

[Manux]

Hi Guys,

I got a spreadsheet with this info in it:

Gramatik J_Tr J_w8 J_w57 J_w10 J_w58 J_w52

Steuersatz Computer wie wird das Wetter Übermorgen

Gramatik J_Tr J_w8 J_w57 J_w10 J_w58 J_w47

Steuersatz Computer wie wird das Wetter Heute

Gramatik J_Tr J_w45 J_w50 J_w47
Steuersatz Computer Wetterbericht für Heute

Gramatik J_Tr J_w8 J_w15 J_w48 J_w59
Steuersatz Computer wie ist die Windstärke

Gramatik J_Tr J_w8 J_w60 J_w48 J_w56
Steuersatz Computer wie sind die Windverhältnisse

Gramatik J_Tr J_w63 J_w49 J_w61 J_w62
Steuersatz Computer welche Mondphase haben

Gramatik J_Tr J_w49
Steuersatz Computer Mondphase

…

As you can see there are cells with “J_wNUMBER” the NUMBER starts with 1 and goes up. I want to be able to find always the highest number in the whole spreadsheet with the text prefix “J_w”.

With the text prefix, the cells are not formated as Numbers, so DMAX did not work

In this example the highest value would be: J_w63 (in its own cell)

Best solution would be if in one cell with the formular, the highest value would appear. But it would be also ok if i just jumped to highest value or that it gets highlighted…

How can this be done ?

Many thanks

[Lupp]

Among the examples posted here this one may be the most “funny” one.

However it was the starting point for a funny finger exercise, too.

Technically essential for any solution might be an array-formula working on prepared text snippets. Of course a solution will be much syntax dependent. I attach an example. Searching the “whole spreadsheet” is not practical for more than one reasons a serious one of which is that a spreadsheet in Calc has more than 10^9 cells in its “logical area”. Another reason for which I wouldn’t try working with the whole spraedsheet is that a lot of actions editing the sheet will be blocked or produce #REF! errors. Let’s be a little bit modest!

ask37428Strange001.ods

PS

The example contained German words. If you are German and you may want additional exchange about this, please change to the /de/ section of the forum. It’s much easyer for me to write in German.

[Manux]

Hello Lupp,
Im indeed German but if i change to the German section, i cant see my question anymore??! Should i repost them in German then ? Is this allowed ?

I have some troubles expanding the area for the number counting step it keeps throwing VALUE at me but i just did make the rectangle bigger so it would include more data…

I try to make the two boxs bigger from the blue cell to include more data and everytime i change it it throws VALUE at me. WHY cant it handle a coordination change?

[Lupp]

Yes. You found a solution already as I see.
Additional explanation and a second example were added in an answer posted ion the German section.