Obtain the value in a cell offset form the first cell of a named range
 

I am trying to figure out a formula which obtains the value of a cell which is offset from the first cell of a named range.

I have tried something like this =OFFSET(RngName,-13,3)

but I get a #REF! error.



David

Obtain the value in a cell offset form the first cell of a named range
 

Hi David,

Am Mon, 23 Nov 2015 11:13:49 -0800 (PST) schrieb
:

I am trying to figure out a formula which obtains the value of a cell which is offset from the first cell of a named range.

I have tried something like this =OFFSET(RngName,-13,3)

you have to create the OFFSET from the first cell of your range.
Try:
=OFFSET(INDEX(rngName,1),-13,1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Obtain the value in a cell offset form the first cell of a named range
 

Try...

I have tried something like this =OFFSET(index(RngName,1,1),-13,3)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Obtain the value in a cell offset form the first cell of a named range
 

Hi Garry,

Am Mon, 23 Nov 2015 14:28:02 -0500 schrieb GS:

I have tried something like this =OFFSET(index(RngName,1,1),-13,3)

that is the better solution. I did not realize that the named range can
have more than one column.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Obtain the value in a cell offset form the first cell of a named range
 

Thanks what you have provided works but now I realized why I was having problems - the name of the named range is not RngName but rather the value of the contents of RngName (RngName is a single cell).

How does that change things?

Sorry for the confusion with this.

David


On Monday, November 23, 2015 at 12:32:35 PM UTC-7, Claus Busch wrote:
Hi Garry,

Am Mon, 23 Nov 2015 14:28:02 -0500 schrieb GS:

I have tried something like this =OFFSET(index(RngName,1,1),-13,3)

that is the better solution. I did not realize that the named range can
have more than one column.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Obtain the value in a cell offset form the first cell of a named range
 

Okay - with your help I was able figure out that all I needed to was add Indirect as in ....

=OFFSET(INDEX(INDIRECT(RngName),1,1),-11,3)

David



On Monday, November 23, 2015 at 12:55:36 PM UTC-7, wrote:
Thanks what you have provided works but now I realized why I was having problems - the name of the named range is not RngName but rather the value of the contents of RngName (RngName is a single cell).

How does that change things?

Sorry for the confusion with this.

David


On Monday, November 23, 2015 at 12:32:35 PM UTC-7, Claus Busch wrote:
Hi Garry,

Am Mon, 23 Nov 2015 14:28:02 -0500 schrieb GS:

I have tried something like this =OFFSET(index(RngName,1,1),-13,3)

that is the better solution. I did not realize that the named range can
have more than one column.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Obtain the value in a cell offset form the first cell of a named range
 

Hi David,

Am Mon, 23 Nov 2015 11:55:31 -0800 (PST) schrieb
:

Thanks what you have provided works but now I realized why I was having problems - the name of the named range is not RngName but rather the value of the contents of RngName (RngName is a single cell).

How does that change things?

if RngName is the name for a single cell
=OFFSET(rngName,-13,1)
works for me as expected.
Check in the name manager if RngName is really a single cell.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Obtain the value in a cell offset form the first cell of a named range
 

Yes what you posted does work - it's just that I wasn't trying to offset from the named range (RngName) but rather offset from the range named after the value of RngName.


On Monday, November 23, 2015 at 1:05:07 PM UTC-7, Claus Busch wrote:
Hi David,

Am Mon, 23 Nov 2015 11:55:31 -0800 (PST) schrieb
:

Thanks what you have provided works but now I realized why I was having problems - the name of the named range is not RngName but rather the value of the contents of RngName (RngName is a single cell).

How does that change things?

if RngName is the name for a single cell
=OFFSET(rngName,-13,1)
works for me as expected.
Check in the name manager if RngName is really a single cell.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional