Problem with formula creation

[uSlackr]

I am using importexcel to build an analysis workbook for our scan data. At one point, I add a column to a sheet and set the columns cells to include a formula using this code:

$sheet.InsertColumn(3,1)
 Set-Excelrange -Address $sheet.Cells["C2:C$($sheet.Dimension.Rows)"] -Formula '=IFERROR(VLOOKUP(B2,Inventory!A:A,1,0),"Not In")'

This all works well. When I open the workbook, the column is there and the formula is calculated correctly. However, when I close the WB, it prompts me to save (even if I don't make a change) and if I say yes, I get this error:

"One or more formulas in this workbook are longer than the allowed 8192 chars. Cell 'sheet1!c2' contains one of these formulas"

If I look a the formula, it looks fine. Bus if I edit and save, the error goes away for that cell. On save I get same error, but for next cell. \\Greg

[uSlackr]

So I went into the workbook, "edited" the formula in C2 then copied it all the way down. When I click close on the workbook, Excel closes without prompting to save!

That aside, I reset the formulas by "editing" and copying on the four tabs like this and the workbook is fine.

[dfinke]

Possibly post a repro PS script?

[uSlackr]

Here you go. This is a very stripped version of the data, but the issue still occurs. TestBook.xlsx

 $excel = Open-ExcelPackage "TestBook.xlsx"
 $sheet = $excel.Workbook.Worksheets["sheet1"]  
 if ($sheet.cells["A1"].value -ne "No Data"){
            $sheet.InsertColumn(3,1)
            Set-Excelrange -Address $sheet.Cells["C2:C$($sheet.Dimension.Rows)"] -Formula '=IFERROR(VLOOKUP(B2,Inventory!A:A,1,0),"Not In")'
            #Set-Excelrange -Address $sheet.Cells["C:C"] -Formula '=IFERROR(LOOKUP(B1,Inventory!A:A),"Not In")'
            Set-excelrange -address $sheet.cells["C1"] -value "In Inventory" -autosize 
        }

 Close-ExcelPackage $excel

[uSlackr]

Any thoughts on this one?

[dfinke]

No idea. Get the same behavior.

@jhoneill anything off the top of your head on this?

[jhoneill]

That is weird If I just have the Vlookup part of the formula, I get error on save ... (I don't get prompted for save, but I expect the first time the formula is processed Excel should may see it as change). If I change the Vlookup to point the same sheet ... no error. If I define part of the the inventory page as a named range , and use the range name, no error.

I've looked working and broken versions and they look identical, which suggests what ever bug there is in EPPplus is quite subtle.

[uSlackr]

I can try the named range if I can do it in powershell.

\Greg

---

From: jhoneill notifications@github.com Sent: Monday, August 12, 2019 4:48:51 PM To: dfinke/ImportExcel ImportExcel@noreply.github.com Cc: uSlackr gmartin@gmartin.org; Author author@noreply.github.com Subject: Re: [dfinke/ImportExcel] Problem with formula creation (#651)

That is weird If I just have the Vlookup part of the formula, I get error on save ... (I don't get prompted for save, but I expect the first time the formula is processed Excel should may see it as change). If I change the Vlookup to point the same sheet ... no error. If I define part of the the inventory page as a named range , and use the range name, no error.

I've looked working and broken versions and they look identical, which suggests what ever bug there is in EPPplus is quite subtle.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/dfinke/ImportExcel/issues/651?email_source=notifications&email_token=AGEO6ZYXQJZBPH2KG3LFUA3QEHEDHA5CNFSM4IKUZ2RKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4DZBPA#issuecomment-520589500, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AGEO6Z4VV2IUYK6UTJVGT7LQEHEDHANCNFSM4IKUZ2RA.

[dfinke]

There is an -AutoNameRange on Export-Excel.

Here is how you can add it. If you do a -PassThru, should be able to get to that collection and add names.

https://github.com/dfinke/ImportExcel/blob/master/Export-Excel.ps1#L1280

[uSlackr]

I’ll give it a shot. Thanks!

\Greg

---

From: Doug Finke notifications@github.com Sent: Monday, August 12, 2019 8:45:53 PM To: dfinke/ImportExcel ImportExcel@noreply.github.com Cc: uSlackr gmartin@gmartin.org; Author author@noreply.github.com Subject: Re: [dfinke/ImportExcel] Problem with formula creation (#651)

There is an -AutoNameRange on Export-Excel.

Here is how you can add it. If you do a -PassThru, should be able to get to that collection and add names.

https://github.com/dfinke/ImportExcel/blob/master/Export-Excel.ps1#L1280

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/dfinke/ImportExcel/issues/651?email_source=notifications&email_token=AGEO6ZYKTPESTNHA663RTLDQEH74DA5CNFSM4IKUZ2RKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4EG6II#issuecomment-520646433, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AGEO6Z4LGXY3SGJMZCGL2EDQEH74DANCNFSM4IKUZ2RA.

[uSlackr]

I switched the code over to create and use named ranges and no longer have an issue. Thanks for the help.

Anything we should report to the EPPlus folks?

[wilby]

I ran into this same issue, still a bug in EPPlus. The link for AutoNameRange above is broken. I ended up using Open-ExcelPackage and calling into the Names.Add function to add named ranges. You can do this on a Workbook or Worksheet.

$excelPackage = Open-ExcelPackage -Path $filePath
$ws_bench = $excelPackage.Workbook.Worksheets['Benchmark']
$ws_ports =  $excelPackage.Workbook.Worksheets['Portfolio']

$excelPackage.Workbook.Names.Add("BenchmarkVLook", $ws_bench.Cells["`$A`$10`:`$G`$18"])
$excelPackage.Workbook.Names.Add("VlookBB25", $ws_bench.Cells["B`$25"])

#Then just use the name in a formula like you would in Excel for a named range.
$ws_ports.Cells["r12:r$rows"].Formula = "(VLOOKUP($H12,BenchmarkVLook,2,FALSE)-VlookBB25)/100"

Just leaving this here in case it helps someone else.

[dfinke]

Thanks for the update!

Just a note, less syntax:

$excelPackage = Open-ExcelPackage -Path $filePath

$excelPackage.Workbook.Names.Add("BenchmarkVLook", $excelPackage.Benchmark.Cells['$A$10:$G$18'])
$excelPackage.Workbook.Names.Add("VlookBB25", $excelPackage.Portfolio.Cells['B$25'])