Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula explanation (Excel2grand)

    Can someone tell me what this formula does.
    INDIRECT(ADDRESS($A27,Branch!$C$2,,,C$1)
    I read the help file for both but don't exactly understand.
    Thanks,
    Scott

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula explanation (Excel2grand)

    The ADDRESS function has 5 arguments:
    - Row number. In your formula, this is taken from cell A27 on the sheet containing the formula.
    - Column number. This is taken from cell C2 on the worksheet named Branch.
    - Absolute/relative. Since this argument is omitted in your formula, absolute is used.
    - Type of reference. Since this argument is omitted in your formula, ADDRESS will return an A1 style reference.
    - Sheetname. This is taken from cell C1 on the sheet containing the formula.

    Let's say that A27 contains 5, Branch!C2 contains 4, and C1 contains "Sheet2". The ADDRESS function will return the address of the cell in row 5, column 4 on Sheet2, i.e.
    Sheet2!$D$5

    The INDIRECT function returns the value of Sheet2!$D$5.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula explanation (Excel2grand)

    Hans,
    Thanks.
    Can you explain Absolute/Relative
    and
    A1 Style(type of reference)
    Scott

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula explanation (Excel2grand)

    Say that you have the simple formula =2*A1 in cell B1. This is a relative reference. If you fill down B1 to B2, B3 etc., the formula will become =2*B2, =2*B3 etc. Relative references are automatically adapted by Excel. If you enter the formula as =2*$A$1, the reference is absolute. If you fill down B1 to B2, B3 etc., the formula remains =2*$B$1. Absolute references are not adapted when you copy a formula. It is also possible to make only the row reference absolute: =2*A$2, or only the column reference: =$A2. When you are entering or editing a formula, you can cycle between the four types by selecting a cell reference and pressing F4.

    By default, Excel uses A1-style cell references: a letter (A, B, C, ...) or two letters (AA, AB, ...) are used to indicate the column, and a number (1, 2, ...) to indicate the row. It is also possible to use R1C1-style references. For example, R7C4 is the cell in the 7th row and 4th column (D7 as an A1-style reference). You can set your preference for one or the other in the General tab of the Tools | Options... dialog.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula explanation (Excel2grand)

    Look in Excel help under "About cell and range references." Also, see Chip Pearson explanations, The INDIRECT Function and Relative And Absolute References. And, just my two cents: a workbook that uses either the Indirect or Address functions probably was not designed properly. I have built lots of workbooks <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> and I have never said during the design, "I think that I will use the Indirect function." No, rather, I've been given another person's workbook and had to add to it and used the Indirect function as a "quick and dirty" solution. I say quick and dirty because when you use Indirect, you have lost Excel's ability to correct all of the referencing addresses when you move a piece of data. If you move a piece of data that you have constructed the address for and referenced with Indirect, then your formula will no longer work. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula explanation (Excel2grand)

    Thanks, Sam and Hans
    I didn't design the workbook. It got put in my corner to modify.
    Lucky Me.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula explanation (Excel2grand)

    > I didn't design the workbook
    That's what I figured. It's good that you are looking at the Indirect, try to figure out all of the possible ranges that it could refer to, because if you move any of these ranges, INDIRECT will be INCORRECT. Worst case scenario is that someone else has already modified it and now you have to figure out why it doesn't work. Let us know if we can help. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula explanation (Excel2grand)

    I'm unencumbered by knowledge here: I don't think that I have ever used the ADDRESS function. And, I am derailing the thread, so ignore this bunny trail, Scott. But, I don't see that the Absolute/relative nor the Type of reference have any use. Since Excel cannot/willnot trace the address generated by the ADDRESS function, who cares whether it is absolute/R1C1/etc.? Am I missing something? Seems like you would always leave these two parameters blank. --Sam <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula explanation (Excel2grand)

    You are thinking of the combination of indirect with address and how that does not matter. Address, by itself, will yield different results when not used with indirect. If you want the address for another reason, then they might be used. [Though, I tend to use them together, when I use them]

    Steve

Posting Permissions

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