Function Address()

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.




Syntax:


= Address (Row_num, Column_num, Reference_type, Address_type, Sheet_name)


Row_num: This value determines the row number that the address should refer to
Column_num: This value determines the column number that should be referred by the address
Reference_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' reference
Address_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 sheet
Sheet_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.
Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

21 comments:

  1. This piece of writing is in fact a nice one it helps new web people,
    who are wishing for blogging.
    Also see my web site - Guitar Cabinet Cases

    ReplyDelete
  2. Hello to every body, it's my first pay a quick visit of this website; this webpage contains awesome and in fact good information for readers.
    Stop by my weblog ; futures trading demo

    ReplyDelete
  3. Hello to every body, it's my first pay a quick visit of this webpage; this website consists of remarkable and really good stuff designed for visitors.
    Have a look at my website : Apple Valley

    ReplyDelete
  4. Malaysia & Singapore & brunei ideal on-line blogshop for wholesale
    & quantity korean accessories, accessories, earstuds,
    pendant, rings, bracelet, hair & bracelet accessories.

    Offer 35 % wholesale price cut. Ship Worldwide
    Have a look at my web site ... how to get ripped

    ReplyDelete
  5. Malaysia & Singapore & brunei ideal on-line blogshop for wholesale & quantity korean add-ons, accessories, earstuds,
    pendant, rings, trinket, bracelet & hair add-ons. Offer
    35 % wholesale markdown. Ship Worldwide
    My web page ; stitching wire indonesia

    ReplyDelete
  6. Malaysia & Singapore & brunei ultimate on-line blogshop
    for wholesale & supply korean add-ons, accessories,
    earstuds, necklace, rings, trinket, hair & bracelet add-ons.
    Deal 35 % wholesale price cut. Ship Worldwide
    Here is my weblog curso unas de gel

    ReplyDelete
  7. Excellent blog here! Also your site loads up fast!

    What host are you using? Can I get your affiliate link to your host?

    I wish my web site loaded up as quickly as yours lol
    My page: michigan unemployment office locations

    ReplyDelete
  8. Your method of telling everything in this paragraph is actually pleasant, every one
    be capable of simply be aware of it, Thanks a lot.
    my web page - Full Piece of writing

    ReplyDelete
  9. 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

    ReplyDelete
  10. 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

    ReplyDelete
  11. 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

    ReplyDelete
  12. Can you tell us more about this? I'd love to find out more details.

    Feel free to visit my web page; FDA approved diet and appetite suppressors in Miami

    ReplyDelete
  13. 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..

    Look into my web-site :: wii u rumors

    ReplyDelete
  14. Thanks for finally writing about > "Function Address()" < Liked it!

    Take a look at my web page - wii u rumors
    my page - wii u news

    ReplyDelete
  15. 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

    my webpage; Kill My Day
    Also see my website :: random rants

    ReplyDelete
  16. Good day! Would you mind if I share your blog with my facebook group?
    There's a lot of people that I think would really enjoy your content. Please let me know. Thanks

    Here is my web page: website desain

    ReplyDelete
  17. 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!

    Feel free to visit my web blog ... best adjustable dumbbells

    ReplyDelete
  18. I feel that is one of the such a lot important information for me.

    And i am satisfied studying your article. But wanna observation on few basic things, The website taste is ideal, the articles is truly nice : D.

    Good activity, cheers

    Here is my webpage healthy cooking

    ReplyDelete
  19. Hi there! Do you know if they make any plugins to protect against hackers?
    I'm kinda paranoid about losing everything I've worked hard on.
    Any recommendations?

    Also visit my homepage :: Manchester Health Clinic

    ReplyDelete
  20. Hi, i think that i saw you visited my weblog so i came to “return the favor”.
    I am attempting to find things to enhance my site!
    I suppose its ok to use some of your ideas!!

    Feel free to visit my page: Health

    ReplyDelete
  21. Saved as a favorite, I love your blog!

    My blog Amateur Porn

    ReplyDelete

Monday, July 20, 2009

Function Address()

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.




Syntax:


= Address (Row_num, Column_num, Reference_type, Address_type, Sheet_name)


Row_num: This value determines the row number that the address should refer to
Column_num: This value determines the column number that should be referred by the address
Reference_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' reference
Address_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 sheet
Sheet_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.

Function Address()SocialTwist Tell-a-Friend

21 comments:

Anonymous,  December 22, 2012 at 3:11 PM  

This piece of writing is in fact a nice one it helps new web people,
who are wishing for blogging.
Also see my web site - Guitar Cabinet Cases

Anonymous,  December 23, 2012 at 6:06 AM  

Hello to every body, it's my first pay a quick visit of this website; this webpage contains awesome and in fact good information for readers.
Stop by my weblog ; futures trading demo

Anonymous,  December 23, 2012 at 6:21 AM  

Hello to every body, it's my first pay a quick visit of this webpage; this website consists of remarkable and really good stuff designed for visitors.
Have a look at my website : Apple Valley

Anonymous,  January 7, 2013 at 12:10 PM  

Malaysia & Singapore & brunei ideal on-line blogshop for wholesale
& quantity korean accessories, accessories, earstuds,
pendant, rings, bracelet, hair & bracelet accessories.

Offer 35 % wholesale price cut. Ship Worldwide
Have a look at my web site ... how to get ripped

Anonymous,  January 7, 2013 at 8:19 PM  

Malaysia & Singapore & brunei ideal on-line blogshop for wholesale & quantity korean add-ons, accessories, earstuds,
pendant, rings, trinket, bracelet & hair add-ons. Offer
35 % wholesale markdown. Ship Worldwide
My web page ; stitching wire indonesia

Anonymous,  January 10, 2013 at 3:23 PM  

Malaysia & Singapore & brunei ultimate on-line blogshop
for wholesale & supply korean add-ons, accessories,
earstuds, necklace, rings, trinket, hair & bracelet add-ons.
Deal 35 % wholesale price cut. Ship Worldwide
Here is my weblog curso unas de gel

Anonymous,  January 15, 2013 at 12:13 AM  

Excellent blog here! Also your site loads up fast!

What host are you using? Can I get your affiliate link to your host?

I wish my web site loaded up as quickly as yours lol
My page: michigan unemployment office locations

Anonymous,  January 21, 2013 at 5:19 AM  

Your method of telling everything in this paragraph is actually pleasant, every one
be capable of simply be aware of it, Thanks a lot.
my web page - Full Piece of writing

Anonymous,  January 24, 2013 at 8:22 AM  

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

Anonymous,  January 28, 2013 at 10:50 PM  

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

Anonymous,  February 1, 2013 at 2:53 PM  

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

Anonymous,  February 19, 2013 at 6:31 AM  

Can you tell us more about this? I'd love to find out more details.

Feel free to visit my web page; FDA approved diet and appetite suppressors in Miami

Anonymous,  February 26, 2013 at 5:34 AM  

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

Look into my web-site :: wii u rumors

Anonymous,  February 26, 2013 at 5:34 AM  

Thanks for finally writing about > "Function Address()" < Liked it!

Take a look at my web page - wii u rumors
my page - wii u news

Anonymous,  March 5, 2013 at 2:18 AM  

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

my webpage; Kill My Day
Also see my website :: random rants

Anonymous,  March 7, 2013 at 6:11 AM  

Good day! Would you mind if I share your blog with my facebook group?
There's a lot of people that I think would really enjoy your content. Please let me know. Thanks

Here is my web page: website desain

Anonymous,  March 7, 2013 at 7:20 AM  

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!

Feel free to visit my web blog ... best adjustable dumbbells

Anonymous,  March 11, 2013 at 6:58 PM  

I feel that is one of the such a lot important information for me.

And i am satisfied studying your article. But wanna observation on few basic things, The website taste is ideal, the articles is truly nice : D.

Good activity, cheers

Here is my webpage healthy cooking

Anonymous,  March 27, 2013 at 1:08 AM  

Hi there! Do you know if they make any plugins to protect against hackers?
I'm kinda paranoid about losing everything I've worked hard on.
Any recommendations?

Also visit my homepage :: Manchester Health Clinic

Anonymous,  March 27, 2013 at 1:08 AM  

Hi, i think that i saw you visited my weblog so i came to “return the favor”.
I am attempting to find things to enhance my site!
I suppose its ok to use some of your ideas!!

Feel free to visit my page: Health

Anonymous,  May 23, 2013 at 2:47 PM  

Saved as a favorite, I love your blog!

My blog Amateur Porn

Post a Comment