none
Change text to Column reference in a function RRS feed

  • Question

  • I am trying to change text to a column reference in a function.

    For example:

    =SUMIF(B:C,"U/S",CHAR(CELL("col",E5)+96)&":"&CHAR(CELL("col",E5)+96))

    The result of "CHAR(CELL("col",E5)+96)&":"&CHAR(CELL("col",E5)+96))" is E:E and that is what I want the sum column to be in my functions.

    =SUMIF(B:C,"U/S",E:E)

    Can it be done without using VBA?

    Thank you.

    Wednesday, August 20, 2014 8:20 PM

Answers

  • You can use the INDIRECT function for this:

    =SUMIF(B:C,"U/S",INDIRECT(CHAR(CELL("col",E5)+96)&":"&CHAR(CELL("col",E5)+96)))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by gdowns Wednesday, August 20, 2014 8:38 PM
    Wednesday, August 20, 2014 8:25 PM

All replies

  • You can use the INDIRECT function for this:

    =SUMIF(B:C,"U/S",INDIRECT(CHAR(CELL("col",E5)+96)&":"&CHAR(CELL("col",E5)+96)))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by gdowns Wednesday, August 20, 2014 8:38 PM
    Wednesday, August 20, 2014 8:25 PM
  • Awesome. Thank you very much.
    Wednesday, August 20, 2014 8:38 PM