OpenXML excel sheet creation. Formula does not work automatically when I use Indirect to reference a list

Question

I am creating an excel template using OpenXML through c# for users to populate and import into our system.

Part of the process is for the user to select options from dropdowns and hidden columns are populated with the Id's of those selections using VLOOKUP formula. All of the ones with direct list references work fine.

I have one instance where the selection from a list called "Application Group" drives what list is used in the next column called "Application". This is done using the INDIRECT function in excel and works fine. The problem comes when trying to get the id for the dynamic list. I will post the code below but put simply opening the excel and selecting an option from application group correctly puts the correct list in Application. Selecting application does not populate the applicationid column automatically. If I select the applicationid column to see the formula is is fine. If I click into the edit box at the top of excel and then tab out of it the formula then triggers and will work from then on. Once again this is a VLOOKUP using the INDIRECT to creat the reference to the list.

I've done a few tests and it seems the INDIRECT is the issue as if I hard code the list name the vlookup triggers as it should.

I also removed the substitute functions but this did not make any difference.

I've checked the calculation mode on the column and it is set to automatic.

I've already searched online but found nothing relevant

                // Application Group
                cell = new Cell() { CellReference = "AI" + rownumber };
                cell.CellValue = new CellValue();
                cellFormula = new CellFormula()
                {
                    Text = "VLOOKUP(AH" + rownumber.ToString() + " ,ApplicationGroupList,2,FALSE)"
                };
                cell.CellFormula = cellFormula;
                row.Append(cell);

                // Application
                cell = new Cell() { CellReference = "AK" + rownumber };
                cell.CellValue = new CellValue();
                cellFormula = new CellFormula()
                {
                    Text = "VLOOKUP(AJ" + rownumber.ToString() + ",INDIRECT(SUBSTITUTE(SUBSTITUTE(AH" + rownumber.ToString() + ",\" \",\"\"),\"/\",\"\") & \"Id\") ,2,FALSE)"
                };
                cell.CellFormula = cellFormula;
                row.Append(cell);

There are no error messages and the formula is correct as simply clicking in the edit box triggers the formula and it works from then on. It seems like excel is not recognising it as a formula even though it was set up in the same way as others that work fine. See the code above. Application group works fine. Application doesn't but the formula created is fine syntactically and functionally.

Does anyone have any experience of something similar and how to get the formula to work as all the others do.

John King John King · Accepted Answer · 2019-06-06T09:30:12

Ok well as seems often to be the case I've spent ages looking at this myself. Exhausted all the options, or so I thought. Just after I post this I think I'll just try this thing I've not had to do for any of the other formula just in case. Bingo!

So I had to add

cellFormula.CalculateCell = true;

And this seemed to solve the problem. Why I needed it I don't know as everything else seems to work without explicitly setting this flag but problem seems to be solved.

OpenXML excel sheet creation. Formula does not work automatically when I use Indirect to reference a list

1 Answers