Ask Your Question
0

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

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 close merge delete

1 Answer

Sort by » oldest newest most voted
0

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!

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.

edit flag offensive delete link more

Comments

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 144 times

Last updated: Jul 24 '14