Addig function to excel doesn't work

PowerShell Help
1

I came across this forum while searching for a problem described here: https://forums.powershell.org/t/getting-filter-in-excel-from-powershell-formula/16648

I used the formula2 field and it does work to enter formulas, although when I check the excel file itself, the field displays a #NAME?
When I click on the field and then once into the edit bar thing and press Enter, it calculates everything correctly so my guess is that it does enter the formula into the field but doesn’t actually go and calculate it? It is kind of annoying and I can’t find a solution nor the reason as why this is happening, so I figured maybe there are some people here that could have a solution :slight_smile:

This is the code I use in my script:

#titles for totals
$PatternTotal = "Daily Total",
                "Weekly Total",
                "Monthly Total",
                "Total All"
#formulas for totals
$formulaValue = ("=SUMME(B" + $newRow + ":G" + $newRow + ")"),
                ("=SUMME(I" + $newRow + ":N" + $newRow + ")"),
                ("=SUMME(P" + $newRow + ":V" + $newRow + ")"),
                ("=SUMME(H" + $newRow + ";O" + $newRow + ";W" + $newRow + ")")
#insert totals
foreach ($i in 0..4) {
    $col = ($WorkSheet.Columns.Find($PatternTotal[$i]))

    $WorkSheet.cells.item($newRow, $col.Column).Formula2 = $formulaValue[$i]
}

EDIT: The original code for row-selection and so on is from here https://pastebin.com/wwjictGS

Thanks a lot already

2

Maybe this will help ??

3

I might have something to help. This is just a simple code bite and I am hoping it helps.

$ExcelAutomation = New-Object -ComObject Excel.Application

# I had to break up the Excel formula =COUNTA($A:$A)+1 because Powershell saw the $ and wanted a variable.
# To find the last row with no data but all the cells have to have a value or the last cell with
# with no data will report as the final row.
$ExcelAutomation.Cells.Item(1, 11).Formula = "=COUNTA($" + "A:$" + "A)"
4

Doesn’t seem to be working, I added it just before I safe the file and it still shows #NAME? but the formula is in the field correctly

5

That’s not really what I was looking for as the formula is correctly inserted in the Ecxel sheet, but just not showing the result of the formula

6

I found what was wrong, even though it does not make sense

My Excel is in German so when you type in formulas you use the german words (e.g. =SUMME). But when you add formulas externally (e.g. my PS-script), everything needs to be in english (e.g. =SUM) and so it doesn’t know =SUMME but when I click into the field it realizes that language is german and can calculate it.

It does make sense that everything is in english (but why don’t english formulas work with german…) but still really weird.