Function Address()

This function is used to custom build a address range in Excel. It converts a text into actual address based on given parameters. The function can return address for a single cell and a range of cell as well.

Row_num: This value determines the row number that the address should refer toColumn_num: This value determines the column number that should be referred by the addressReference_type: This single-digit parameter determines which type of reference should the address have. The reference codes are - 1=absolute reference, 2=absolute row/relative column reference, 3=relative row/absolute column reference and 4=relative reference. The default reference is 'Absolute' referenceAddress_type: This value determines the style of address that should be returned. There is no effect of this parameter on its functioning. The address type codes are - 1 or TRUE = A1 type reference, 0 or FALSE = R1C1 style reference. In case this parameter is not available, the function adopts the A1 style reference, even if R1C1 is the default style in the sheetSheet_name: As the name suggests, this parameter provides the sheet name which should be included in the address as its part. This is used when the address should refer to a range which is external to the given sheet

Note: In the above syntax, Reference type, Address Type and Sheet name are optional parameters. Any of these can be mentioned in the function independent of each other

Examples:

Here are a few examples of using Address() function

Address(1,2) - result will be: $B$1

Address(2,1) - result will be: $A$2

Address(2,1,1) - result will be: $A$2

Address(2,1,2) - result will be: A$2 (note the reference style, the reference to column 'B' is not absolute)

Address(2,1,4) - result will be: A2

Address(2,1,1,1) - result will be: $A$2

Address(2,1,1,TRUE) - result will be: $A$2

Address(2,1,1,0) - result will be: R2C1

Address(2,1,1,FALSE) - result will be: R2C1

Address(2,1,1,1,Sheet1) - result will be: Sheet!$A$2 (note that the address now contains sheet name also)

Address(2,1,1,1,Sheet_new) - result will be: Sheet_new!$A$2

Address(2,1,,1) - result will be: $A$2 (note that the third parameter has been ignore, but its place holder remains there as a blank indicated by 2 commas)

Application:

The address function is generally used where a cell range is used, but the range is dynamic in itself. Mostly, it used with the Indirect() function. The Indirect() function is used to convert the text address into actual reference.

For example, in a worksheet, a column should populate values which are dependent on a certain condition. Now this requires the range of cells to be dynamic in the way that it should dynamically change based on the condition mentioned. Here is an example of the application of Address() function with Indirect() function.

A motivating discussion is definitely worth comment.I do believe that you should write more about this subject, it might not be a taboo matter but generally people don't speak about these issues. To the next! Cheers!!Here is my blog post : click through the following page

Hi there! I could have sworn I've visited this site before but after looking at some of the articles I realized it's new to me.Nonetheless, I'm certainly pleased I discovered it and I'll be bookmarking it and checking back often!Stop by my web site ... Ways to Become a Millionaire

Good day I am so thrilled I found your website, I really found you by accident, while I was browsing on Askjeeve for something else, Nonetheless I am here now and would just like to say many thanks for a tremendous post and a all round thrilling blog (I also love the theme/design), I don't have time to go through it all at the minute but I have saved it and also included your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the awesome job.My website - Gambling

I'm extremely impressed with your writing abilities as neatly as with the format on your blog. Is that this a paid subject matter or did you customize it yourself? Anyway keep up the nice quality writing, it is uncommon to peer a nice blog like this one these days..

Wonderful beat ! I would like to apprentice at the same time as you amend your web site, how can i subscribe for a blog web site? The account aided me a applicable deal. I were a little bit familiar of this your broadcast offered vibrant transparent concept

Hi there! I could have sworn I've been to your blog before but after going through many of the posts I realized it's new to me.Anyways, I'm definitely delighted I found it and I'll be book-marking it and checking back often!

Monday, July 20, 2009

This function is used to custom build a address range in Excel. It converts a text into actual address based on given parameters. The function can return address for a single cell and a range of cell as well.

Row_num: This value determines the row number that the address should refer toColumn_num: This value determines the column number that should be referred by the addressReference_type: This single-digit parameter determines which type of reference should the address have. The reference codes are - 1=absolute reference, 2=absolute row/relative column reference, 3=relative row/absolute column reference and 4=relative reference. The default reference is 'Absolute' referenceAddress_type: This value determines the style of address that should be returned. There is no effect of this parameter on its functioning. The address type codes are - 1 or TRUE = A1 type reference, 0 or FALSE = R1C1 style reference. In case this parameter is not available, the function adopts the A1 style reference, even if R1C1 is the default style in the sheetSheet_name: As the name suggests, this parameter provides the sheet name which should be included in the address as its part. This is used when the address should refer to a range which is external to the given sheet

Note: In the above syntax, Reference type, Address Type and Sheet name are optional parameters. Any of these can be mentioned in the function independent of each other

Examples:

Here are a few examples of using Address() function

Address(1,2) - result will be: $B$1

Address(2,1) - result will be: $A$2

Address(2,1,1) - result will be: $A$2

Address(2,1,2) - result will be: A$2 (note the reference style, the reference to column 'B' is not absolute)

Address(2,1,4) - result will be: A2

Address(2,1,1,1) - result will be: $A$2

Address(2,1,1,TRUE) - result will be: $A$2

Address(2,1,1,0) - result will be: R2C1

Address(2,1,1,FALSE) - result will be: R2C1

Address(2,1,1,1,Sheet1) - result will be: Sheet!$A$2 (note that the address now contains sheet name also)

Address(2,1,1,1,Sheet_new) - result will be: Sheet_new!$A$2

Address(2,1,,1) - result will be: $A$2 (note that the third parameter has been ignore, but its place holder remains there as a blank indicated by 2 commas)

Application:

The address function is generally used where a cell range is used, but the range is dynamic in itself. Mostly, it used with the Indirect() function. The Indirect() function is used to convert the text address into actual reference.

For example, in a worksheet, a column should populate values which are dependent on a certain condition. Now this requires the range of cells to be dynamic in the way that it should dynamically change based on the condition mentioned. Here is an example of the application of Address() function with Indirect() function.

A motivating discussion is definitely worth comment.I do believe that you should write more about this subject, it might not be a taboo matter but generally people don't speak about these issues. To the next! Cheers!!Here is my blog post : click through the following page

Hi there! I could have sworn I've visited this site before but after looking at some of the articles I realized it's new to me.Nonetheless, I'm certainly pleased I discovered it and I'll be bookmarking it and checking back often!Stop by my web site ... Ways to Become a Millionaire

Good day I am so thrilled I found your website, I really found you by accident, while I was browsing on Askjeeve for something else, Nonetheless I am here now and would just like to say many thanks for a tremendous post and a all round thrilling blog (I also love the theme/design), I don't have time to go through it all at the minute but I have saved it and also included your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the awesome job.My website - Gambling

I'm extremely impressed with your writing abilities as neatly as with the format on your blog. Is that this a paid subject matter or did you customize it yourself? Anyway keep up the nice quality writing, it is uncommon to peer a nice blog like this one these days..

Wonderful beat ! I would like to apprentice at the same time as you amend your web site, how can i subscribe for a blog web site? The account aided me a applicable deal. I were a little bit familiar of this your broadcast offered vibrant transparent concept

Hi there! I could have sworn I've been to your blog before but after going through many of the posts I realized it's new to me.Anyways, I'm definitely delighted I found it and I'll be book-marking it and checking back often!