[Solved] Search in a Dynamic Range

[arghmeinaugen]

Hi,

I am looking for a formula to create a dynamic range, search that range and return the value in a different cell.

Please the attached.

Dynamic Range Search Example.ods

(46.16 KiB) Downloaded 96 times

Any help, much appreciated.

Thanks,

[Zizi64]

INDIRECT() function??

[Villeroy]

OFFSET is the function you want. OFFSET(start_ref ; rows_off ; cols_off ; row_resize ; col_resize)

But first of all you want a proper database

[arghmeinaugen]

Thanks for your replies.

I have tried OFFSET, but either can't understand it, or can't make it work. Any assistance on this is greatly appreciated.

We do have a proper database, however we have fields for PART NO, MANUFACTURER and DESCRIPTION. The list that we have been given is poorly formatted for import and we need to format so that we can upload.

[Lupp]

This is a follow-up to your previous question in viewtopic.php?f=9&t=101551.

Since you didn't comment on my suggestions made there, in specific to the usage of functions not available in AOO, I'm doubting if it is resonable to take the time for suggestions again relying on advanced functions. I will now attach a reworked document demonstrating how you could do it in LibO Calc V 6.2 or higher. If you are decisive to NOT try LibO, please tell me. I wouldn't like to waste additional time.

The attachment opened in your AOO also shows what you would get with LibO. Have a look into it.
The attached also demonstrates how to use OFFSET successfully for the purpose.

If you want to try REGEX(), but are not ready to confirm the assurances concerning the SYNTAX of you PartNumber information, you are wlecome, of course, with additional questions. Using RegEx of a spcecific flavor may require a bit of guided learning if the user isn't yet familiar ith the topic.

aoo101564DynamicRangeSearchExample_1.ods

(30.44 KiB) Downloaded 85 times

[arghmeinaugen]

Hi,

Thank you for your reply, it's much appreciated.

I have downloaded Libre Office and have open the document, copying in the full list. It is working, just when filling down it mentions in a pop up: You cannot change only part of an array. I'm not sure how to edit the array range, but the last row of the list is 16642. Greatly appreciated if you are able to advise how to change this.

Regarding the other topic, I managed to work this out myself and then once I had I closed the thread as solved. Again thanks for replying.

[keme]

The attached file does not depend on array calculations, nor the availability of the REGEX() function, so it should work in any flavor of the software suite and allow you to edit more freely. However, it does depend on regular expressions so you need to enable that in Calc:

• Menu item: Tools - Options
• Branch in left pane: Calc - Calculate
• Tick the option to Use regular expressions ...

Beware: This option may alter the behavior of other calculations (compare/search operations, mostly).

[arghmeinaugen]

Thanks, that is all solved. Much appreciated.

I will copy and paste the part numbers before entering them to the main spread sheet, then use Index&Match to make sure they're in the right place.

Thanks all

[Lupp]

Thank you for your reply, "arghmeinaugen".
I'll take the opportunity to also make the situation clearer to other visitors to this thread. You surely feel free to draw your own conclusions. I definitely don't want to act as an advertising vehicle for LibreOffice. I simply assumed, as a user of OpenOffice you would know about the behaviour of formulas entered for array-evaluation under filling with the help of the drag-handle and the mouse.

Yes. The result you reported is strange (unwelcome) and I don't like it, but we/they (you and me/the developers) cannot change it because it is eventually established.

Filling an array-formula into additional cells by dragging expands its range for application and ouput. This isn't exactly incongruous because many array-formulas are designed for output to a range (more than one cell based on a single recalculation) anyway. However, the kind of array-formula I'm using preferrably (and thus the kind occurring in my example) needs array-evaluation only for itermediary steps, and outputs to a single cell then. This case should (imo) be recognized and respectively handled by the filling process - but it isn't. Filling down (e.g.) such a formula for a single column by dragging expands the output range as if it had a locked range in advance. 

The fill procedure itself is well capable of the needed recognition, however. If you drag down the formula together with an additional cell from a neighbouring column, it works as we would like it in the simpler case, too. This (the "need it for one - drag two" behaviour) even holds if the additional cell is blank. The recognition also is done correctly if we fill down without dragging via the menu. This is common heritage of AOO and LibO.

In LibO the mentioned "perceived misbehaviour" does'nt occur to me often since I mostly use the shortcut Crl+D when I want to fill down something. This proceeding is immune against the unwelcome result. Unfortunately AOO hasn't a default shortcut for the purpose.

Also in AOO you can use the modifier Ctrl+ when dragging to get the Ctrl+D effect. I would prefer to customize my AOO to make it know the Ctrl+D as I want it.