none
Formula applied to the cell of a column shows only the result but not the formula, where other cells of the column showing the consequent formula applied in the formula bar. RRS feed

  • Question

  • I am working with Excel Addin. I am using Vsto 2013 and excel 2016 version as tools for the development.
    I have to implement formula as one column in excel sheet.
    Here we want to get the formula from the frontend using c#.
    The problem here is that I am unable to get the formula at the particular cell where I gave some formula, rather than it is showing all the cells in the formula format only.

    I am using the below code to unlock the column from the sheet. but not given any specific formula in the back end.

     Excel.Range formularng = worksheet.Range["J" + startRowIndex, "J" + lastrowcnt] as Excel.Range;
                    formularng.Locked = false;
                    formularng.ColumnWidth = 10;
    We have to give the formula from the excel UI.

    The problem here is "The formula which i am giving for a particular cell, it is showing only the value but not the result" and the precedent formula is applied to all the other cells of that column and i am able to see the formula for those cells.

    Any help would be appreciated.

    Thanks


    Tuesday, March 28, 2017 10:17 AM

All replies

  • Hello,

    How do you insert the formula and what is the formula of the particular cell?

    What value does it show and what should be expected result?

    Normally, we could use Range.Formula Property (Excel) to return the formula(if exists) or the constant.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 29, 2017 6:23 AM
    Moderator
  • Hi

    we have to enter the formula manually in a cell for excel sheet. an example can be like "=k16+k17". When I select that particular cell after entering the formula, the cell is showing only the result value but not the formula in the formula bar which is applied manually.

    Let me know if any further clarification required regarding the question.

    Thanks


    Wednesday, March 29, 2017 1:53 PM
  • Hello,

    If you want to display the formula in Excel cell, you could go to Formulas tab and click Show Formula in the Formula Auditing group. For more information, please visit Display or hide formulas

    To get the same result in your add-in, we could set its Window.DisplayFormulas Property (Excel) into true.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 30, 2017 2:27 AM
    Moderator
  • Hi 

    I tried setting this  ActiveWindow.DisplayFormulas = true ;

    But still the cell for which I am entering the formula manually i.e., from excel, it is showing only the result there.

    But not the Formula.

    I am using Listobject to bind the data to excel. Is there any issue with this listobject?

    Thanks

    Friday, March 31, 2017 4:29 AM
  • Hello,

    If only this kind of cells which bound to listobjects fail to show formula, I think the issue may cause from the listobject.

    How do you bind the listobject? What do you bind? I suggest you share detail steps then we could try to reproduce and see if there is any solution or workaround.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 31, 2017 9:10 AM
    Moderator
  • Hi

    Here is the code, that I am using to work with listobject and binding the data

    // Unlocking the Sheet UnProtect(worksheet); Excel.Range lstObjRange = worksheet.Range["B2" , "N50"] as Excel.Range; // Sample Data Range lstObjRange.Clear(); string listobjectname = "ListObject" + shtName; lo = worksheet.Controls.AddListObject(lstObjRange, listobjectname); string[] fields = new string[] { "Formula", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", }; for (int i = 0; i < lo.ListColumns.Count; i++) { lo.ListColumns[i + 1].Name = fields[i]; }

    // Binding the Data to sheet BindingSource bs = new BindingSource(); bs.DataSource = dtSource; lo.SetDataBinding(bs, "", fields); // locking the sheet ProtectSheet(worksheet); // For Unlocking Formula Column Excel.Range formularng = worksheet.Range["B2", "B50"] as Excel.Range; formularng.Locked = false; formularng.ColumnWidth = 10;


    Please let me know, if any information needed indetails.

    Thanks

    Friday, March 31, 2017 5:19 PM
  • I made a test with your code, and it seems I could reproduce your issue. In my option, this is the expected result. The formula column will show up as string when it is banded to excel range. It set the value instead of formula. 

    Is the formula for the “Formula” column same? If so, I would suggest you add below code to reset formula after you bind the object. For this way, you could not provide value for Formula column. Otherwise, the first cell would be Formula string.

                lo.DataBodyRange.Cells[lo.ListRows.Count, lo.ListColumns["Formula"].Index].FormulaR1C1 = "=Sum(2,2)";

    If the formulas are different, I am afraid, you could not set them. As you set a formula for a table object in Excel, once you set a formula in a cell, all of other cells in the same column will use the same formula.

    Here are my test code:

            private void DataBinding_Click(object sender, RibbonControlEventArgs e)
            {
                Worksheet worksheet = Globals.Factory.GetVstoObject(
             Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
                // Unlocking the Sheet
                //UnProtect(worksheet);
    
                Excel.Range lstObjRange = worksheet.Range["B2", "C4"] as Excel.Range; // Sample Data Range
                lstObjRange.Clear();
    
                string listobjectname = "ListObject";            
    
                var lo = worksheet.Controls.AddListObject(lstObjRange, listobjectname);
    
                string[] fields = new string[] { "Formula",
                                  "Jan"
                                };
                for (int i = 0; i < lo.ListColumns.Count; i++)
                {
                    lo.ListColumns[i + 1].Name = fields[i];
                }
    
    
                // Binding the Data to sheet
                List<FormualClass> dtSource = new List<FormualClass>();
                dtSource.Add(new FormualClass {   Jan ="J1"});
                dtSource.Add(new FormualClass { Jan = "J2" });
                BindingSource bs = new BindingSource();
                bs.DataSource = dtSource;
                lo.SetDataBinding(bs, "", fields);          
                lo.DataBodyRange.Cells[lo.ListRows.Count, lo.ListColumns["Formula"].Index].FormulaR1C1 = "=Sum(2,2)";
    
            }
            public class FormualClass
            {
                public string Formula { get; set; }
                public string Jan { get; set; }
            }
    


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 7:18 AM
    Moderator
  • Hi

    I have tried with your code, but the issue still exists, when I am trying to give the formula manually in a cell that is covered by listobject.

    I will explain the scenario, how this should be working.

    The listobject will have twelve columns with month names as Jan, Feb, Mar,..., Dec.

    These 12 columns are covered by listobject.

    In these 12 columns the user should be feasible to give the formula manually.

    The Issue is:

    When I am trying to give the formula in a cell manually in any of the column, I am able to see only the value but not the formula which i have given.

    so, how can I resolve this Issue.

    This requirement is of high priority.

    Please help me out.

    Thanks

    Monday, April 10, 2017 5:40 AM
  • Thanks for more information.

    I made a test with this, and I could reproduce your issue. It seems to be related with depth implementation of Table Column Formula. I tried to find a workaround, but I failed.

    Since user will change the formula, I will suggest you write value instead of formula in Table.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 7:44 AM
    Moderator
  • Hi

    Thanks for your reply.

    can you explain me what you meant by this statement:

    " I will suggest you write value instead of formula in Table"

    Thanks

    Tuesday, April 11, 2017 8:49 AM
  • Don't write formula in Table to avoid unexpected result.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 9:03 AM
    Moderator
  • I am not writing any formula from programming.

    only user will be giving that in excel.

    so how can I restrict the user to do that?

    As they needed this as one functionality to be working.

    Thanks.

    Tuesday, April 11, 2017 9:08 AM
  • It seems I misunderstood your post. I suggest you try below code to set table data.

                Worksheet worksheet = Globals.Factory.GetVstoObject(
           Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
                Excel.Range lstObjRange = worksheet.Range["B2", "C4"] as Excel.Range; // Sample Data Range
                lstObjRange.Clear();
                string listobjectname = "ListObject";
                var lo = worksheet.Controls.AddListObject(lstObjRange, listobjectname);
                string[] fields = new string[] { "Formula",
                                  "Jan"
                                };
                for (int i = 0; i < lo.ListColumns.Count; i++)
                {
                    lo.ListColumns[i + 1].Name = fields[i];
                }           
                // Binding the Data to sheet
                List<FormualClass> dtSource = new List<FormualClass>();
                dtSource.Add(new FormualClass { Jan = "J1" });
                dtSource.Add(new FormualClass { Jan = "J2" });
                lo.DataSource = dtSource;


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 9:22 AM
    Moderator
  • Hi

    Thanks for the response.

    but I have used this code previously as mentioned above and the issue was still existing.

    how can i resolve the issue using the same code again?

    Help me out.

    Thanks.

    Tuesday, April 11, 2017 9:25 AM
  • I made a test with above code by following below steps.

    1. Add a table by running above code

    2. Change one of the cell in the table by formula

    3. All the cells in the column will be change to formula

    Could you share us how did you reproduce your issue with above code?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 9:36 AM
    Moderator
  • Hi I have a doubt with your steps.

    Can you clarify me?

    In the second step, after applying the formula in a cell, are you able to see the formula in the formula bar when you select the cell in which you have applied the formula?

    These are the steps which I have followed.

    1. Added the table by running the code

    2. In the column, if the data is available, then if I apply any formula, then the formula is applied to only that cell. But not to the entire column.

    3. If the column has no data, then if i apply any formula, then that formula is applied to all the other cells in the column.

    I don't see the formula for the cell in which I have given the formula.

    can you please verify from your side whether you're able to see the formula for the applied cell in the formula bar or not?

    so that you can suggest me.

    Thanks.


    Tuesday, April 11, 2017 10:03 AM
  • By following your steps, I could reproduce your issue.

    I am afraid there is no way to workaround this issue.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 12, 2017 8:53 AM
    Moderator
  • Hello

    I have a similar issue.

    Have any of the solution worked? 

    Please kindly advise if you have a solution.

    Friday, June 7, 2019 9:14 AM