Getting the cell value a named cell refers to

Posted by: lpagliaro on 9 November 2017, 11:02 am EST

  • Posted 9 November 2017, 11:02 am EST

    Hello. I'm trying to get the cell value a named cell refers to but I'm not being able to figure out how to do it.

    For example, if I create a named cell:

    ...
    workbook.Names.Add("test", "=$1$A");
    ...

    and then I do this to try to get the value this named cell I created refers to:

    ...
    var names = workbook.Names;
    var name = names["test"];
    cellValue = name.RefersTo;

    cellValues contains the following string: "='Bravo Co '!$A$1"

    How do I do to get the value of the cell referenced to using the string from cellValues?

    Thanks.

    Regards.
  • Replied 9 November 2017, 11:10 am EST

    lpagliaro said:

    cellValues contains the following string: "='Bravo Co '!$A$1"


    Bravo Co is the name of the sheet.

    lpagliaro said:

    How do I do to get the value of the cell referenced to using the string from cellValues?


    By value I mean the calculated value, so if the named cell refers to a cell that has a formula I want the calculated value.
  • Replied 10 November 2017, 11:37 am EST

    Hello,

    You can get the Cell index by using RefersTo property of of Name. Then you can use the cell index to get the cell value using the code as follows:

    string cellname = name.RefersTo;
    // you would need to modify/truncate the string to get correct cell index e.g 'A1'.
    string cellvalue= worksheet.Range[cellname].Value.ToString();


    I hope it helps.

    Thanks,
    Deepak Sharma
  • Marked as Answer

    Replied 10 November 2017, 11:57 am EST

    Thanks for the help! It worked.
Need extra support?

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

Learn More

Forum Channels