Get Values from List Validation Formula

Get Values from List Validation Formula

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

• 

  maya.halter

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 31 March 2022, 9:51 pm EST

  Sorry i forgot to mention, we are using GrapeCity.Documents.Excel Version 4.2.6

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  maya.halter

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.