SAC Excel Addin - Referencing SAC Table cells in formulas inside of Table not possible?

[mfrieser]

Hello,

I am currently working with the SAP Analytics Cloud Addin for Microsoft office.

I want to build some operating use case, where I insert a SAC table and then reference with Excel formula an Excel area / cell in my workbook.

What I experienced is, that if my excel formula references another cell in the same SAC table, the formula will be "forgotten" after I process the data.

e.g. If i lookup a value with VLOOKUP and the search criteria is in my SAC table as an dimension, the formula is forgotten as soon as i process the data.

If i use a helping column with the search criteria outside the SAC table (also with reference on SAC table values), the formula is still there, even after processing the data.

Is this a bug or a intended behaviour?

Best regards

Michael

[N1kh1l · Accepted solution]

michael-frieser

Currently Inside the Table Area any Excel formula referring to cells /ranges outside the table will not be retained if done on main table non data columns references like dimensions data (products) for but you can add a column from ribbon and then place the formula referring only to data region it will work and will get copied down correctly

You get a warning like this if referring to the table non data column in formula

If you refer to a helper column, the formula stays but the references does not copied down.

Variance column is fine as it refers to data cells only

Vlookup using helper , the formula stays and gets copied but the reference is static

I think its area of improvement for the Add-in which should be raised.

Nikhil

[mfrieser · Accepted solution]

Thanks Nikhil for the good explanation.

As I assumed, currently the helper column is the only way to kepp the formula after processing.

Michael