If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Referencing relative to a formula reference
I have an Excel workbook with two worksheets. In one
sheet (Books) I have defined a series of items with column 1 being my book Id and column 4 being the book title. In the second sheet (ISBN) I may have multiple entries per line of the first sheet, since a given book may have multiple editions, each with its own ISBN number. In column 2 of ISBN I have the BookId from the first sheet, which I have as an absolute reference (eg. the formula would be something like =Books!R4C1). Now here's my problem. I'd like to have a column in ISBN which reflects the book's title that is indicated by the reference in column 2 (BookId). I thought I could do something along the lines of =OFFSET(INDIRECT(RC2,FALSE),0,3) but INDIRECT is quite unhappy, and all the functions I remember for dealing with formulas are for macro sheets (eg. GET.CELL(6,R2C)). Now I can get the desired effect by doing =OFFSET(Books!R1C4,MATCH(RC2,Books!C1,0)-1,0) but this seems computationally burdensome on Excel, and I have to wonder if there isn't a more direct way I'm overlooking. There is a second issue. Using the method in the above paragraph works, except that when the returned string has a hard newline (that I previously entered via Alt+Enter), this is shown as a box character and no longer shows as a newline. Any tips? Thanks, Csaba Gabor from Vienna |
Thread Tools | |
Display Modes | |
|
|