 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2007Reference cells in named range
I have a large sheet with data. One of the columns is defined as a named range (Key_x). On another sheet, I would like to reference to some cells in that column. Is it possible to use something like =Key_x!5 to get the value of the 5th cell (row) in this named range? Thanks for any hints. Hi Luca, You could use OFFSET, as follows. RANHGE("KEY_X").OFFSET(nRows,0) if in the same column, HTH, Sharon > I have a large sheet with data. One of the columns is defined as a named > range (Key_x). [quoted text clipped - 3 lines] > to get the value of the 5th cell (row) in this named range? > Thanks for any hints. Sharon, thanks for your reply. I tried =RANGE("key_x").OFFSET(2;0) which is your formula adjusted to my needs. I tried as well =OFFSET(key_a; 2; 0) because the OFFSET formula seems to take three parameters. But Excel keeps on telling me that the formula contains an error. What am I missing?? BTW, using Excel XP and I don't talk about VBA. > Hi Luca, > [quoted text clipped - 12 lines] >> to get the value of the 5th cell (row) in this named range? >> Thanks for any hints. Hi Luca, I was meaning within VBA. Sorry for that! If in Excel , is almost the same, give a try to: =OFFSET(key_a, 2, 0) Take care with colons or semicolons, depending what country configuration you have. HTH > Sharon, thanks for your reply. > [quoted text clipped - 27 lines] > >> to get the value of the 5th cell (row) in this named range? > >> Thanks for any hints. I already tried different things using the OFFSET formula. But didn't succeed. Have you really got it working?? > Hi Luca, > [quoted text clipped - 39 lines] >>>> to get the value of the 5th cell (row) in this named range? >>>> Thanks for any hints. =OFFSET(key_a, 0, 0) would work as formula, but of course makes no sense... > I already tried different things using the OFFSET formula. But didn't > succeed. Have you really got it working?? [quoted text clipped - 39 lines] >>>>> to get the value of the 5th cell (row) in this named range? >>>>> Thanks for any hints. Hi Luca, Sure I've it working! You may publish an example of what are you doing... By the way, did you try something like =key_a ?? (it should work the same as =OFFSET(key_a,0,0) .... Sharon > I already tried different things using the OFFSET formula. But didn't > succeed. Have you really got it working?? [quoted text clipped - 42 lines] > >>>> to get the value of the 5th cell (row) in this named range? > >>>> Thanks for any hints. I don't do anything more than explained in my first post. I have a column defined as a named range. On another sheet I try to reference specific cells (based on the row index) in that range. I just don't see why I fail to get the value of the second row in the named column using =OFFSET(key_a,1,0) I always get a REF error. What am I doing wrong? > Hi Luca, > [quoted text clipped - 54 lines] >>>>>> to get the value of the 5th cell (row) in this named range? >>>>>> Thanks for any hints. Just "=key_a" works (as well as "=OFFSET(key_a,0,0)") but is not what I need. I want to be able to define a different row index. > Hi Luca, > [quoted text clipped - 54 lines] >>>>>> to get the value of the 5th cell (row) in this named range? >>>>>> Thanks for any hints. So, if I understand well: =key_a works =offset(key_a,0,0) works AND =offset(key_a,3,0) DOESN'T WORK Am I right? If so, my Excel knowledge doesn't go so far ..... Sharon > Just "=key_a" works (as well as "=OFFSET(key_a,0,0)") but is not what I > need. I want to be able to define a different row index. [quoted text clipped - 57 lines] > >>>>>> to get the value of the 5th cell (row) in this named range? > >>>>>> Thanks for any hints. Correct. I now even tested it on another machine using Excel 2007. Same behavior. > So, if I understand well: > [quoted text clipped - 72 lines] >>>>>>>> to get the value of the 5th cell (row) in this named range? >>>>>>>> Thanks for any hints. Luca, I think I got it! If range name involves more than one cellm, then you need =offset(key_a,0,0,nRows,0) Give it a try! HTH, Sharon > Correct. I now even tested it on another machine using Excel 2007. Same > behavior. [quoted text clipped - 75 lines] > >>>>>>>> to get the value of the 5th cell (row) in this named range? > >>>>>>>> Thanks for any hints. didn't do it either for me... anyway, thanks for your time! > Luca, > [quoted text clipped - 89 lines] >>>>>>>>>> to get the value of the 5th cell (row) in this named range? >>>>>>>>>> Thanks for any hints. Fri, 2 Nov 2007 04:28:00 -0700 from sharon <sharon_scott@msn.com>: > Luca, > [quoted text clipped - 3 lines] > > =offset(key_a,0,0,nRows,0) I think you mean that last parameter (number of columns) to be a 1. For that matter, unless we're talking about an array formula I think the penultimate parameter (number of rows) also needs to be a 1.  SignatureStan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ How about using INDEX like "=INDEX(te,5)" instead of using OFFSET ? keizi > Just "=key_a" works (as well as "=OFFSET(key_a,0,0)") but is not what I > need. I want to be able to define a different row index. [quoted text clipped - 51 lines] >>>>>>> to get the value of the 5th cell (row) in this named range? >>>>>>> Thanks for any hints. >I have a large sheet with data. One of the columns is defined as a named >range (Key_x). [quoted text clipped - 3 lines] >to get the value of the 5th cell (row) in this named range? >Thanks for any hints. =INDEX(named_range,2,1) will refer to row 2 in the named range If named_range only has a single column, then the column argument is optional. --ron you rock! thank you very much, ron. >> I have a large sheet with data. One of the columns is defined as a named >> range (Key_x). [quoted text clipped - 10 lines] > If named_range only has a single column, then the column argument is optional. > --ron >you rock! thank you very much, ron. You're welcome. Glad to help. Thanks for the feedback. --ron Luca, Sharon was on the right lines with her suggestions I just think there were some misunderstandings. The format for OFFSET is - =OFFSET (Range, RowOffset,ColOffset, ReturnHeight,ReturnWidth) So for your need you would use offset(key_a,1,4,1,1) that will return the item in col 1 and row 5 of your range..... (offset starts counting at zero hence why 4 will return the 5th row) Hope this helps David >I have a large sheet with data. One of the columns is defined as a named >range (Key_x). [quoted text clipped - 3 lines] > to get the value of the 5th cell (row) in this named range? > Thanks for any hints. Thanks for the info. > Luca, > [quoted text clipped - 20 lines] >> to get the value of the 5th cell (row) in this named range? >> Thanks for any hints.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.