[Solved] Search in a Dynamic Range

Discuss the spreadsheet application

[Solved] Search in a Dynamic Range

Postby arghmeinaugen » Sun Apr 05, 2020 11:23 am

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 20 times


Any help, much appreciated.

Thanks,
Last edited by Hagar Delest on Mon Apr 06, 2020 1:47 pm, edited 1 time in total.
Reason: tagged solved
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Search in a Dynamic Range

Postby Zizi64 » Sun Apr 05, 2020 12:01 pm

INDIRECT() function??
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9439
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Search in a Dynamic Range

Postby Villeroy » Sun Apr 05, 2020 12:44 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Search in a Dynamic Range

Postby arghmeinaugen » Sun Apr 05, 2020 1:20 pm

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.
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Search in a Dynamic Range

Postby Lupp » Sun Apr 05, 2020 1:39 pm

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 10 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search in a Dynamic Range

Postby arghmeinaugen » Mon Apr 06, 2020 8:55 am

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.
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Search in a Dynamic Range

Postby keme » Mon Apr 06, 2020 9:32 am

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).
Attachments
Dynamic Range Search.ods
(22.09 KiB) Downloaded 12 times
User avatar
keme
Volunteer
 
Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Search in a Dynamic Range

Postby arghmeinaugen » Mon Apr 06, 2020 9:51 am

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
Open Office 4.1.3 on Mac Sierra
arghmeinaugen
 
Posts: 11
Joined: Sat Apr 04, 2020 11:10 am

Re: Search in a Dynamic Range

Postby Lupp » Mon Apr 06, 2020 1:55 pm

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.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 10 guests