Formula explanation (Excel2grand)

Topic

New Reply

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

Replies

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

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

Reply | Quote

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

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

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

Reply | Quote

> 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

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

Reply | Quote

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