• Formula explanation (Excel2grand)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Formula explanation (Excel2grand)

    Author
    Topic
    WSready4data
    AskWoody Lounger
    December 19, 2005 at 11:20 am #427550

    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

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      December 19, 2005 at 11:32 am #990954

      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.

      Reply | Quote
      • WSready4data
        AskWoody Lounger
        December 19, 2005 at 1:50 pm #990975

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

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          December 19, 2005 at 2:02 pm #990977

          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.

          Reply | Quote
        • WSSammyB
          AskWoody Lounger
          December 19, 2005 at 2:10 pm #990980

          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 gramps 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

          Reply | Quote
          • WSready4data
            AskWoody Lounger
            December 19, 2005 at 2:24 pm #990981

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

            Reply | Quote
            • WSSammyB
              AskWoody Lounger
              December 19, 2005 at 2:58 pm #990988

              > 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

              Reply | Quote
      • WSSammyB
        AskWoody Lounger
        December 19, 2005 at 3:12 pm #990993

        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 confused

        Reply | Quote
        • WSsdckapr
          AskWoody Lounger
          December 19, 2005 at 3:48 pm #991005

          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

          Reply | Quote
    Viewing 0 reply threads
    Reply To: Formula explanation (Excel2grand)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.