 |
 | 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 / Worksheet Functions / March 2008Referring to a variable cell on another sheet
In a cell on Sheet A, I want to display the value of a cell on Sheet B. I know the column that it is in (R), but not the row - the row should be equal the last row in that column which has a nonblank value. In other words, if the first six rows of column R on Sheet B have values, then I want to display the value of 'Sheet B'!R6. I think I can do this using INDIRECT(something) and COUNTA('Sheet B'!R:R), but I can't seem to fit them together in the correct manner. Thanks! =LOOKUP(2,1/(SheetB!R1:R65535<>""),SheetB!R1:R65535) Press CTRL+SHIFT+ENTER. not just ENTER. Hope this helps. Thanks Gaurav > In a cell on Sheet A, I want to display the value of a cell on Sheet B. I > know the column that it is in (R), but not the row - the row should be [quoted text clipped - 8 lines] > > Thanks! Sorry...this would work even if you press just ENTER. Sorry for the confusion. > In a cell on Sheet A, I want to display the value of a cell on Sheet B. I > know the column that it is in (R), but not the row - the row should be [quoted text clipped - 8 lines] > > Thanks! Cool, it works - got a minute to explain HOW in the world it works? I understand the basic format of LOOKUP() - what are the 65535s and the <>"" for? > Sorry...this would work even if you press just ENTER. Sorry for the > confusion. [quoted text clipped - 11 lines] > > > > Thanks! Ah, wait - it occurs to me that 65,535 is the largest number that can be represented in unsigned 16-bit binary... > Cool, it works - got a minute to explain HOW in the world it works? > [quoted text clipped - 16 lines] > > > > > > Thanks! Alright, this problem has become a little bit more complex, and I need some more help... I'm working in two cells in SheetA - A2, where this new formula will go, and A1, which determines A2's value. I was constrained to Row R of SheetB before - I no longer know the row that I need beforehand. Instead, I need to do a LOOKUP, checking Row1 of SheetB for a value that matches the value of cell A1 in the current sheet. Then I need to return the value of the last nonblank cell in that row (there won't be any blank cells between filled cells, so the row of the last filled cell will equal COUNTA(that column)). So if A1's value is "Purple", and SheetB looks like this: Blue | Purple | Green 185 | 24812 | 3481 571 | 48194 | 1734 281 | 48194 | 5819 556 | 58602 | ... I need to return the value 58602. Thanks again to anyone that takes the time to help me out. > Ah, wait - it occurs to me that 65,535 is the largest number that can be > represented in unsigned 16-bit binary... [quoted text clipped - 19 lines] > > > > > > > > Thanks! |
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.