Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
relative offset to a named cell | Excel Worksheet Functions | |||
how do i use the text in a cell as a named range in a vlookup form | Excel Discussion (Misc queries) | |||
Need hyperlink function to obtain range name from a cell (contents | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions |