Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: What does this MS sintax mean

    Can anyone help me with this MS Excel.....
    =INDIRECT(ADDRESS(COLUMNS($CB28:CB28)+7,2,,,$F28))

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    The best way to work this out is to break the formula down into individual components, starting at the middle and working your way out.


    COLUMNS($CB28:CB28) returns the number of columns in $CB28:CB28, which is 1.

    COLUMNS($CB28:CB28)+7 therefore returns 8.



    For our analysis, we can therefore simplify this:

    ADDRESS(COLUMNS($CB28:CB28)+7,2,,,$F28)

    to this

    ADDRESS(8,2,,,$F28)


    The ADDRESS() worksheet function takes many arguments and returns a cell reference as a string. In this case:
    • 8 corresponds to the Row Number
    • 2 corresponds to the Column Number
    • and whatever is in cell F28 corresponds to the sheet name.

    Let's assume F28 contains the word "Sheet1". This would mean that

    ADDRESS(8,2,,,$F28) returns the string "Sheet1!$B$8"


    Therefore, for our analysis, we can simplify the entire formula:

    =INDIRECT( ADDRESS(COLUMNS($CB28:CB28)+7,2,,,$F28) )

    to this:

    =INDIRECT("Sheet1!$B$8")

    The INDIRECT() worksheet function converts a string into a range reference. Therefore INDIRECT("Sheet1!$B$8") will simply return whatever value is held in Sheet1!B8.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •