Retrieving data by dynamic address

[SeliM]

Thank you for taking time to read my enquiry.

I am working with a local government authority that has a third party system to assess submissions for works - such as roads maintenance or drainage systems etc.

(The third-party system does not provide any form of "report" but rather exports a complex workbook that can have hundreds of rows of information - so addresses are required to be by dynamic formula.)

I have been able to construct formulas that "find" specific assessment criteria texts and return a weighting applied to that criterion from a nearby cell. Criterion can be very lengthy say 100 characters or more.

I think it was one of your colleagues who provided the formula I have used:
="Data!"&ADDRESS(MATCH(LEFT(A15,25)&"*",Data!$B:$B,0),3,1) - where "Data" is the name of the WS holding information - A15 holds the criterion - in this case "Support for Local Content Is your organisation within the Shire?"

(BTW - I cannot read the formula provided as the &"*" is unknown to me)

All works well at this point because it can find and use the 25 characters that equates to "Support for Local Content" which is the first of 5 criterion in the section Support for Local Content.

The third party system looks like it uses an [Alt Enter] approach to insert a return within the cell to separate the criteria heading "Support for Local Content" from the criterion text e.g. Outline the economic contribution your organisation will make to the Shire and the wider South-West region.

(I have removed the hard return to collapse the text into one line and the formula works)

Finally my question - is there a formula that can ignore the hard return that seems to give the problem. Here's examples of the criteria:

SOCIAL SUSTAINABILITY:
What are the social sustainability benefits your submission will offer to the community?
(refer Council's Procurement Policy Clause 5.1.6.2)

SUPPORT FOR LOCAL CONTENT
Does your company employ apprentices or trainees from within Colac Otway Shire? If yes, please provide details?
(refer Council's Procurement Policy Clause 5.1.6.3)

(You'll note how the text can very, for example the social sustainability has a colon after while support for local content doesn't)

any advice most welcomed.

Mel

 

[NdNoviceHlp]

Not sure that I understand your needs, but the CLEAN function will remove non printable characters. HTH. Dave

="Data!"&ADDRESS(MATCH(LEFT([B]CLEAN([/B]A15[B])[/B],25)&"*",Data!$B:$B,0),3,1)