topic Re: recherchev and vlookup not accepted in excelxp tagsets . any reason? in Microsoft Integration with SAS

recherchev and vlookup not accepted in excelxp tagsets . any reason?

cnwentsa — Fri, 28 Aug 2015 20:29:22 GMT

Hi there. I am stuck with my code. anyone knows why my formula is not beeing accepted in this ods tagsets.excelxp? I have even tryed the english version (vlookup) of recherchev, but it is not recognized as a formula. ods listing close; ods tagsets.excelxp options(sheet_name="LISTE" gridlines="yes" orientation="landscape" panelcols="1" embedded_titles="NO" frozen_headers="YES" frozen_headers="1" autofilter="yes" filter_colls="_all_" autofilter_table="1" row_heights="75" autofit_heights="no" ); proc report data=reftable.liste nowd ; column actif Portefeuille Conseiller Poste majeure equipe ps Agregat Portefeuille kl ; define Portefeuille /style(header)=vertcl_header style(column)=[just=center]; define equipe /style(column)=[just=center] "Équipe"; define poste /style(column)=[just=center] ; define majeure /style(column)=[just=center]; define ps /style(column)=[just=center] format=fm_num.; define Agregat /style(column)=[just=center] "Agrégat"; define actif /computed style(column) ={tagattr='formula:=RECHERCHEV(RC[1];STRUCTURE_CAISSE!R1C1:R20C1;1;FAUX)'}; run; quit; ods tagsets.excelxp close; Thanks.

Re: recherchev and vlookup not accepted in excelxp tagsets . any reason?

ballardw — Fri, 28 Aug 2015 21:31:51 GMT

Excelxp writes XML and that may be the main issue.

To see what the XML might be, make a base spreadsheet in excel that does what you want. The SAVE AS XML.

Look in the resulting XML file with a text editor like Notepad.

I am not sure if XML supports any look up and that may be the reason that going through the XML translation it gets lost.

Re: recherchev and vlookup not accepted in excelxp tagsets . any reason?

Reeza — Fri, 28 Aug 2015 21:37:45 GMT

Your code isn't formatted and hard to read so I'm not sure I see the Formulas option specified in your tagset options. If not, try that.

Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

FORMULAS

yes

Data values that start with an '=' will become formulas

Re: recherchev and vlookup not accepted in excelxp tagsets . any reason?

Matteo_Sibio — Tue, 06 Dec 2022 18:00:09 GMT

This comment works for me, here below my case:

I had this excel formula to produce:
=(IF(OR(RC[6]>1;RC[8]<0);"ERRORE";"OK"))

In the XML it becomes:
"=IF(OR(RC[6]>1,RC[8]<0),"ERRORE","OK")"