How to insert this formula correct?

[monoeagle]

Hi,

the following AllData tab is the source.

On that tab I want to build an dynamic table for the diagram.

If I use the formular manually it works:

=SUMME(BEREICH.VERSCHIEBEN(AllData!$A$2:$A$128;0;VERGLEICH("o_2020";AllData!$A$1:$AA$1;0)-1))

But if I add this in the formula property of set-excelrange I allways get an error if I try to open the generated excel file with the message remove incorrect parts and the cells are empty.

Set-ExcelRange -Address $ActionsPerYearWorksheet.Cells["B2"] -Formula "=SUMME(BEREICH.VERSCHIEBEN(AllData!$A$2:$A$128;0;VERGLEICH(o_2020;AllData!$A$1:$AA$1;0)-1))"

I tried to escape "$A$2:$A$128" and "$A$1:$AA$1" with ` or make a string out of it $(" ") but without success.
Excel also didn't tell me what it doesn't like.

Is there a special form for the formula property?

greetz

[dfinke]

Yes, that is what I would've suggested. Excel just does not do anything with it?

Would need to see code, data, and xlsx.

[monoeagle]

Here we are. =)

Data.csv
script.txt
Stat.xlsx

[dfinke]

sorry - the data etc are fine. need a simple script that repros the problem.

[monoeagle]

Nope that is what I tried in the uploaded script.txt 2 days ago.

[dfinke]

i would try, build the parts that work. don't insert that formula with o_$yr. print out that string to the console, copy paste it to Excel. get more clues

[monoeagle]

crazy behavior
manual dummy table with one sheet for the data and a second sheet for the results

In the column "C" I wrote the formula text which I used in Column "B"

The result of the manual try in the sheet2.xml is on the right side.

The german formula is translated in english automatically.

[PICTURE DIFF]

But if I use the translated formula manual in the cell B4 it results in #NAME? and in the xml file
_xludf.SUM(_xludf.OFFSET(AllData!$A$2:$A$128,0,_xludf.MATCH("m_2020",AllData!$A$1:$AA$1,0)-1))

He didn't know SUM, OFFSET and MATCH.

The left side of the [PICTURE DIFF] is the generated xlsx file via the import-excel module.

In the script I use the escaped "o_$yr" and in the xml file we can see the result is as expected.
But Excel says that there is a problem.

If I use the formula in german with excaping:

The result in the XML on the left side

The escaping works, but excel already has a problem.

Without the $yr variable --> same error

In the XML File like in [PICTURE DIFF] on the left side the formula is correct but excel want to repair/remove them.

It's crazy!

[dfinke]

Definitely strange. I don't know.

[monoeagle]

I'm digging. ;)

[monoeagle]

Problem Solved!

In General where I had to look at:

• in german the delemiter is ";" in englisch ","
• to build the formula seprately before add it to "-Fomula"

Result is as wanted:

[dfinke]

Wow - so "lanuage pack" influenced this?

[monoeagle]

It looks like that.
Writing german formula in excel manually excel will translate them automatically to write them into the xml. ;)
So it is better to use the english version with generated xlsx. ;)

But also how to write the formula especially if you use variables in the formula.
Put the Formula in a separate variable works for me.