search

open-contracting / field-level-mapping-template

Collects issues for the improvement and generation of the Field-Level Mapping Template.
BSD 3-Clause "New" or "Revised" License
2 stars 1 forks source link

Update lookup formulae for field titles and descriptions in (OCDS) sheets #12

Closed duncandewhurst closed 2 years ago

duncandewhurst commented 3 years ago

The formulae evaluate to a #REF! error when the template is opened in MS Excel.

duncandewhurst commented 3 years ago

Removing the unnecessary INDIRECT functions seems to fix the problem:

=IF(OR(ISERROR(SEARCH("extension",INDIRECT("$A"&row()))),NOT(ISERROR(SEARCH("parties",INDIRECT("$C"&row()))))),VLOOKUP(INDIRECT("$C"&row()),INDIRECT("OCDS Schema 1.1.5!$B:$D"),2,FALSE), VLOOKUP(INDIRECT("$C"&row()),INDIRECT("OCDS Extension Schemas 1.1.5!$B:$D"),2,FALSE))

Should be

=IF(OR(ISERROR(SEARCH("extension",INDIRECT("$A"&row()))),NOT(ISERROR(SEARCH("parties",INDIRECT("$C"&row()))))),VLOOKUP(INDIRECT("$C"&row()),'OCDS Schema 1.1.5'!$B:$D,2,FALSE), VLOOKUP(INDIRECT("$C"&row()),'OCDS Extension Schemas 1.1.5'!$B:$D,2,FALSE))
odscjen commented 2 years ago

fixed in updated version 0.91 published 20220407

odscjen commented 2 years ago

closed wrong issue!

odscjen commented 2 years ago

was right the first time