Get Values from List Validation Formula

Posted by: maya.halter on 31 March 2022, 9:48 pm EST

  • Posted 31 March 2022, 9:48 pm EST

    Hello,

    we have a range in our worksheet that contains a validation of type list.
    Let's say our validation formula is:
    "=IF( Test="abc" , Worksheet1!$A$1:$A$3 , Worksheet1!$B$1:$B$5 )"


    All we want to do is to read the list of values from this formula, which would be either the values A1:A3 or B1:B5 from Worksheet1.
    Could you please tell us how we can achieve this?

    Thanks in advance,
    Maya
  • Replied 31 March 2022, 9:51 pm EST

    Sorry i forgot to mention, we are using GrapeCity.Documents.Excel Version 4.2.6
  • Replied 3 April 2022, 9:37 pm EST

    Hello,

    There is no direct way to do this, so we have created a workaround to achieve the same. Please go through it and let me know if it works for you.


    Regards,
    Prabhat Sharma.
    FormulaCheckDemo.zip
  • Replied 4 April 2022, 2:36 am EST

    Hi Prabhat Sharma,

    thanks for your response.
    Unfortunately, this workaround does not work for us because we want a generic way to get the values from such a formula. In your code snippet you already know beforehand that there is this IF( Test="abc", .. , ..) check, which we do not know in our case.

    All we know is that there is a formula that calculates a list of valid values for that cell (like you see them in the drop down list in the excel file).

    If there is no workaround, could you please implement this functionality for us in the next grapeCity release?

    Thanks in advance,
    Maya
  • Replied 4 April 2022, 5:44 pm EST

    Hello Maya,

    We have escalated this to the development team for investigation and will let you know as soon as we get any update on this from their end?
    [Internal Tracking ID: DOCXLS-5773]

    Regards,
    Prabhat Sharma.
  • Marked as Answer

    Replied 5 April 2022, 8:02 pm EST

    Hello Maya,

    You can use the below given code to get the value of the range returned by the formula:

    IWorksheet worksheet2 = wb.Worksheets[1];
    IRange range1= null;
    for (int i = 0; i < worksheet2.UsedRange.Cells.Count; i++)
    {
    if (worksheet2.UsedRange.Cells[i].HasFormula)
    {
    range1 = (IRange)worksheet2.Evaluate(worksheet2.UsedRange.Cells[i].Formula);
    var value = range1.Value;
    }
    }


    Regards,
    Prabhat Sharma.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels