Excel Address Formula

How to use the Excel Address formula to turn a reference to a cell into the cell address that can be used in other formula like INDIRECT

• 03:27
• Skill Level: 5
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Excel Address Formula

The Excel address formula allows you to create, as text, a reference to a cell. So what does that mean? Basically here, where we have a cell in cell B4. What we want to do is create out of some key inputs where that cell is to be used in other key formulas. So here we are told that the column for this reference is number 2, the row is 4 & it’s on sheet 1. We can make use of the address function, so if we go into the lookup & reference category, you’ll find address & I say ok. You’ll see it asks for a number of items. The first thing it asks for is the row number, so what we want to show, we can see it’s in row 4, so we can either type in a 4 or else in this case I’m going to link it to that cell. You’ll see a 4 pops up there. The next thing it asks for is the column number, so what column is it, one two, again I’m going to link it to that cell. You’ll see immediately it’s already got dollar B dollar 4. The next item, absolute numbers specifies the reference type. So you’ll see if you type a 1 in there, it’s absolute. When I type a 1 in it stays as is, there’s a dollar sign in front of the B & the 4. If it’s a 2, you’ve got an absolute row & a relative column. Let me put a 2 in, & you’ll see that the row is anchored moves & so on. If you put a 3 in, it’s the other way round &a 4 there’s no absolute or relative references. I’m going to leave it like that. The next item, if you want to enter it, is the style. So if you want a letter & a number style you put a 1 in or you omit it. If you want the row 1 column 1 style you put a zero. So if I put a zero in here, you’ll see the referencing changes, I’m going to go back to the 1 & the sheet text is the sheet that you want to specify, again you can type it in or else in this case I’m going to click on there. And when I say ok, you’ll see in text format it gives me reference to a cell, which I can now use in other functions such as the indirect function. Now that we have this formula set up, it’s very easy to change what is shown here. So let’s for example say we don’t want to look at sheet 1, so let’s pretend there was a sheet 2, I can come here, change that to sheet 2, say ok. And now our reference here changes to sheet 2 cell B 4. And if we had other cells linked to it such as the indirect formula, that would affect those formulas. So this function allows you to get out of excel the exact reference syntax so you can use it in other formulas.