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
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.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
The remarkable thing about Adrian's presentation is his ability to further change and adapt the focus of the course as issues arose. He was also able to keep the more skilled delegates challenged...– Alexander Proudfoot Consultants