We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Serach for the highest value with text prefix in the whole sheet [closed]

asked 2014-07-24 15:38:13 +0200

Manux gravatar image

updated 2016-03-09 21:17:27 +0200

Alex Kemp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-14 14:53:26.542388

1 Answer

Sort by » oldest newest most voted

answered 2014-07-24 16:58:20 +0200

Lupp gravatar image

updated 2014-07-24 17:17:13 +0200

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!



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.

edit flag offensive delete link more


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?

Manux gravatar imageManux ( 2014-07-24 17:52:01 +0200 )edit

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.

Lupp gravatar imageLupp ( 2014-07-25 00:07:53 +0200 )edit

Question Tools

1 follower


Asked: 2014-07-24 15:38:13 +0200

Seen: 168 times

Last updated: Jul 24 '14