A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Referencing relative to a formula reference



 
 
Thread Tools Display Modes
  #1  
Old April 26th, 2010, 11:06 AM posted to microsoft.public.excel.worksheet.functions
Csaba Gabor
external usenet poster
 
Posts: 2
Default 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
  #2  
Old April 26th, 2010, 11:24 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Referencing relative to a formula reference

Just use

=VLOOKUP(RC2,Books!C1:C4,4,FALSE)

--

HTH

Bob

"Csaba Gabor" wrote in message
...
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



  #3  
Old April 26th, 2010, 11:53 AM posted to microsoft.public.excel.worksheet.functions
Csaba Gabor
external usenet poster
 
Posts: 2
Default Referencing relative to a formula reference

VLOOKUP requires sorted order of the lookup column, which
was implied to be not the case by my having a third argument
of 0 for MATCH. But even if that were not the case, VLOOKUP
is essentially the same technology as MATCH, HLOOKUP,
and LOOKUP and does not address either of my questions.

Q1: In a spreadsheet, is there any formula that can deal
with the formula in another cell or the reference returned
by such formula (eg. =Books!R4C1)?

Q2: When MATCH returns a cell value that has a hard newline
(entered via Alt+Enter), how does one get that to display as
a newline instead of showing a box symbol?

Just in case, this is on Excel 2003 under Win XP Pro

Csaba

On Apr 26, 12:24 pm, "Bob Phillips"
wrote:
Just use

=VLOOKUP(RC2,Books!C1:C4,4,FALSE)

--

HTH

Bob

"Csaba Gabor" wrote in message

...

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


  #4  
Old April 26th, 2010, 01:13 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Referencing relative to a formula reference


"Csaba Gabor" wrote in message
...
VLOOKUP requires sorted order of the lookup column, which
was implied to be not the case by my having a third argument
of 0 for MATCH. But even if that were not the case, VLOOKUP
is essentially the same technology as MATCH, HLOOKUP,
and LOOKUP and does not address either of my questions.



It doesn't if you use FALSE as the last argument as I did.

It addresses it as I read it, not directly because I believe there is a
better way than your approach, but the objective.


Q1: In a spreadsheet, is there any formula that can deal
with the formula in another cell or the reference returned
by such formula (eg. =Books!R4C1)?

Q2: When MATCH returns a cell value that has a hard newline
(entered via Alt+Enter), how does one get that to display as
a newline instead of showing a box symbol?

Just in case, this is on Excel 2003 under Win XP Pro

Csaba



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.