Row Function in cell reference

[Willmonbo]

I am trying to do something that I think should be fairly easy and perhaps I have even done in the past but can't figure out now what i'm missing.

I just want to reference the cell in column D of the same row that I am working in, how do I do that?

 

[scottylad2]

Ok I get what you need

Excel Workbook
	D	E	F	G
5		INDIRECT(ADDRESS(ROW(),4))
6	555		555	555
7	55		55	55
8	55		55	55
9	11		11	11
10	22		22	22
11	25		25	25
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F6	=MyCell
G6	=MyCell

Excel Workbook
Name	Refers To
MyCell	=INDIRECT(ADDRESS(ROW(),4))
Workbook Defined Names

open the name manager and create a new name, MyCell using the formula<table border="0" cellpadding="0" cellspacing="0" width="192"><tr height="19"><td colspan="3" style="height: 14.4pt; width: 144pt;" height="19" width="192">=INDIRECT(ADDRESS(ROW(),4))

 

[Willmonbo]

that's exactly what I did and for some reason it gives me the #value error when I enter the "defined name" into a cell.

Honestly, I am also hesitant about using the indirect function since i was told by an experienced excel guy on this board, that the indirect function can be quite volatile, perhaps that's even the reason it's not working here, so for now I think the "index" method (submitted by T. Valko above) will do it for me, unless something better is suggested.

Thanks scott.. for your efforts, it truly is appreciated.

 

[jasonb75]

that's exactly what I did and for some reason it gives me the #value error when I enter the "defined name" into a cell.

Honestly, I am also hesitant about using the indirect function since i was told by an experienced excel guy on this board, that the indirect function can be quite volatile, perhaps that's even the reason it's not working here, so for now I think the "index" method (submitted by T. Valko above) will do it for now, unless something better is suggested.

That doesn't mean it's unstable / unrelaible, http://www.decisionmodels.com/calcsecretsi.htm

I've used the principle of =INDIRECT("RC4",0) with no problems, both in sheet, and in named ranges.

 

[T. Valko]

I think the "index" method (submitted by T. Valko above) will do it for me, unless something better is suggested.

There is nothing better!

 

[scottylad2]

in this situation it's not any more unreliable than any other formula, it's referencing a hardcoded Column Address, with a flexible row() number

What the indirect does is create a reference to a cell/range from a text string.

As long as you have something that works for you

 

[Willmonbo]

thanks Jason for correcting me on the definition of "volatile" in functions, I understood it as unstable as you correctly assumed.