Reference current cell in Excel

[rrrdavis07]

How do you reference the cell address of the formula you are creating?

For instance, I want to make a calculation in a certain cell based on a value in the cell to the left.

Both of the following produce an error:

=OFFSET(CELL("address"),0,-1)
=OFFSET(ADDRESS(ROW(),COLUMN()),0,-1)

I think this is because both CELL and ADDRESS functions produce a result formatted as Text. Is there another way to make reference to the current cell?

Randy Davis
Denver, CO

 

[SkipVought]

hi,

rather than generalities, please state SPECIFICALLY what value you have and how that value would be used to reference another cell. ie is the VALUE a number or a text representation of a reference or a string lookup value?

Please be specific.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

If you are setting the formula in code, consider converting it to use R1C1 addressing style. You can then set the .FormulaR1C1 property of the cells in the range.
Range("D510").FormulaR1C1 = "=RC[-1]+4" 'Value in column C + 4

It is also worth noting, contrary to Microsoft documentation, that you can set the .Formula property using the top cell in the range and the relative addresss work as hoped:
Range("D510").Formula = "=C5+4"

For the specific question you asked, the OFFSET function is special in that it can reference itself and not trigger the Circular reference error:
=OFFSET(F4,0,-1) 'Returns value from cell E4

You can also create a named formula to return a relative reference. You do this using the same approach as creating a named range.
1) Select cell F4
2) Create a named formula with a name like CellAtLeft and a refers to formula like:
=E4

You may now use CellAtLeft elsewhere on that same worksheet and the row and column references will update automatically.

Brad

 

[Gavona]

How do you reference the cell address of the formula you are creating?
For instance, I want to make a calculation in a certain cell based on a value in the cell to the left.

If I understand you correctly you don't need to - it is implicit.

If I want the value in cell B1 to be twice the value in A1 then I would use the formula:
=A1*2
If I copy this formula down to C2 then it will automatically change to
=B2*2 still referencing the cell 1 column to the left of the cell containing the formula. This is because I used relative addressing (the default).
You can "anchor" either the row or the column or both by adding $ signs. If my formula in B1 was:
=A$1*2
Then when copied to C2 the formula would be:
==B$1*2.

Look up in help:
About cell and range references. Absolute and relative addresses

By the way it might just help you understand what is going on if you temporarily switch to the R1C1 reference style. Tools, Options, GeneralTab



Gavin

 

[byundt]

If you want the CellAtLeft named formula from my previous Comment to apply to any worksheet, you can use INDIRECT function like this:
=INDIRECT("RC[-1]",FALSE)

Brad

 

[rrrdavis07]

Thanks for the responses. Sorry about not being explicit enough. Sometimes I think I give too much information.

Let say:

B2 65.75
C2 =INDEX({"F";"D";"C";"B";"A"},MATCH(B2,{0;60;70;80;90}))

<Yes, its a grading spreadsheet>

This is easy enough and it works. However, there are several locations in the sheet where this formula needs to be calcuated and it will always use the value to the left of the formula. I could just copy the formula and manually change the reference (B2) to the new location, but there has to be a formula that will do this.

I should be able to use the OFFSET function to refer to the cell directly to the left and not have to manually modify each formula.

=INDEX({"F";"D";"C";"B";"A"},MATCH([COLOR=black yellow]OFFSET(ADDRESS(ROW(),COLUMN()))[/color],{0;60;70;80;90}))

However, this doesn't work. Yes, in the time it took to type this out I could have done it manually, but now I'm just curious.



Randy Davis
The Technology Firm
Denver, CO

 

[byundt]

Randy,
If you copy the cell containing the formula and paste it elsewhere, Excel will update the reference to cell B2 for you automatically. You don't need to go to any such heroic efforts. That's the beauty of using relative addressing.

Brad

 

[rrrdavis07]

Yes, I understand relative addressing. And I've gone ahead and done just that. However, I just trying to better understand the OFFSET function.

So my question still stands. Is there a way to reference the current cell from within another function?


Randy Davis
The Technology Firm
Denver, CO

 

[byundt]

Yes, you can reference the current cell from within another function.

Use OFFSET(F4,0,0) if the current cell is F4. Or use INDIRECT("RC",False) to create a reference to the cell holding the formula. The OFFSET function won't complain (no circular reference error message), and neither will the INDIRECT.

Brad

 

[rrrdavis07]

Thanks, Brad


Randy Davis
The Technology Firm
Denver, CO

 

[byundt]

So your grading formula would become:
=INDEX({"F";"D";"C";"B";"A"},MATCH(INDIRECT("RC[-1]",FALSE),{0;60;70;80;90}))

This formula refers to the cell one column to the left for the MATCH function.

The reason your formula with ADDRESS failed is because ADDRESS returns a string rather than the cell reference. But you could wrap ADDRESS inside INDIRECT:
=INDEX({"F";"D";"C";"B";"A"},MATCH(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),{0;60;70;80;90}))

Brad

 

[SkipVought]

First it is not a best and accepted practice to embed data in formulas, especially arrays, although it works perfectly well. Maintaining tabular data is much easier and better than changing formulas.

Second, a formula in a column should not need to be changed in order to calculate a special circumstance. Either you should consider redesigning your workbook/worksheet structure, redesign your formula. That is what an IF() function does.

So exactly WHAT is it on that row, that logically can be used to "trigger" is special condition? If the answer is "nothing" then the restructure approch might be an option.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]