CELL REFERNECING

  • Thread starter Thread starter heather
  • Start date Start date
H

heather

TRYING TO REFENCE A CELL IN ANOTHER WORKBOOK. TO GET TO THAT I USED
=ADDRESS(MATCH(I30,'[Vendor sales at cost.xlsx]Sheet1'!$I:$I,0),9)),0,1))
WHICH GIVES ME $J$1594
I WANT IT TO RETURN THE ACTUALL DATA THAT IS IN THAT CELL...HOW CAN I DO
THIS? I HAVE TRIED USING =CELL("CONTENTS",FORMULAFROMABOVE) BUT THAT RETURNS
AN ERROR...WHAT AM I DOING WRONG?
 
First of all, don't post using only capitals - it is known as
SHOUTING, is difficult to read, and is considered rude.

Try this:

=INDIRECT(your_formula)

Hope this helps.

Pete
 
Hi,

The result of the ADDRESS function is text not a reference, so you need to
use INDIRECT(ADDRESS(MATCH(I30,'[Vendor sales at
cost.xlsx]Sheet1'!$I:$I,0),9)),0,1)))

However, INDIRECT only works against open workbook (note your example does
not include the path, which suggests the workbook IS open).

Lotus 1-2-3 version 1 supported closed workbook refernces and now Open
Office also does.
 
Two things, at least, you are doing wrong.

Firstly your caps lock key is broken so you are SHOUTING at us.
Secondly, it looks as if you are getting confused between text strings and
cell references. ADDRESS returns a text string, but the CELL function is
looking for a cell reference. =CELL("contents",$J$1594) would be valid but
=CELL("contents","$J$1594") would not.

You probably want the INDIRECT function, rather than the CELL function. But
aren't you going round the houses? MATCH returns a position in an array,
ADDRESS converts that to text, INDIRECT converts the TEXT back to the
content of a cell. Wouldn't it be better to use a lookup function to get
the data directly?
 
WHEN I DO THIS, IT JUST RETURNS AN ERROR MESSAGE SAYING SOMETHING IN MY
FORMULA IS WORNG...

Shane Devenshire said:
Hi,

The result of the ADDRESS function is text not a reference, so you need to
use INDIRECT(ADDRESS(MATCH(I30,'[Vendor sales at
cost.xlsx]Sheet1'!$I:$I,0),9)),0,1)))

However, INDIRECT only works against open workbook (note your example does
not include the path, which suggests the workbook IS open).

Lotus 1-2-3 version 1 supported closed workbook refernces and now Open
Office also does.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


heather said:
TRYING TO REFENCE A CELL IN ANOTHER WORKBOOK. TO GET TO THAT I USED
=ADDRESS(MATCH(I30,'[Vendor sales at cost.xlsx]Sheet1'!$I:$I,0),9)),0,1))
WHICH GIVES ME $J$1594
I WANT IT TO RETURN THE ACTUALL DATA THAT IS IN THAT CELL...HOW CAN I DO
THIS? I HAVE TRIED USING =CELL("CONTENTS",FORMULAFROMABOVE) BUT THAT RETURNS
AN ERROR...WHAT AM I DOING WRONG?
Click to expand...
Click to expand...
 
I tried using the Lookup function but if my number I am looking up doesn't
exsist it returns a number closest to and i don't want that...Is there a way
to format it to give me an exact and if its not there than leav it blank?

David Biddulph said:
Two things, at least, you are doing wrong.

Firstly your caps lock key is broken so you are SHOUTING at us.
Secondly, it looks as if you are getting confused between text strings and
cell references. ADDRESS returns a text string, but the CELL function is
looking for a cell reference. =CELL("contents",$J$1594) would be valid but
=CELL("contents","$J$1594") would not.

You probably want the INDIRECT function, rather than the CELL function. But
aren't you going round the houses? MATCH returns a position in an array,
ADDRESS converts that to text, INDIRECT converts the TEXT back to the
content of a cell. Wouldn't it be better to use a lookup function to get
the data directly?
--
David Biddulph

heather said:
TRYING TO REFENCE A CELL IN ANOTHER WORKBOOK. TO GET TO THAT I USED
=ADDRESS(MATCH(I30,'[Vendor sales at cost.xlsx]Sheet1'!$I:$I,0),9)),0,1))
WHICH GIVES ME $J$1594
I WANT IT TO RETURN THE ACTUALL DATA THAT IS IN THAT CELL...HOW CAN I DO
THIS? I HAVE TRIED USING =CELL("CONTENTS",FORMULAFROMABOVE) BUT THAT
RETURNS
AN ERROR...WHAT AM I DOING WRONG?
Click to expand...
Click to expand...
 
Look in Excel help for VLOOKUP.
--
David Biddulph

heather said:
I tried using the Lookup function but if my number I am looking up doesn't
exsist it returns a number closest to and i don't want that...Is there a
way
to format it to give me an exact and if its not there than leav it blank?

David Biddulph said:
Two things, at least, you are doing wrong.

Firstly your caps lock key is broken so you are SHOUTING at us.
Secondly, it looks as if you are getting confused between text strings
and
cell references. ADDRESS returns a text string, but the CELL function is
looking for a cell reference. =CELL("contents",$J$1594) would be valid
but
=CELL("contents","$J$1594") would not.

You probably want the INDIRECT function, rather than the CELL function.
But
aren't you going round the houses? MATCH returns a position in an array,
ADDRESS converts that to text, INDIRECT converts the TEXT back to the
content of a cell. Wouldn't it be better to use a lookup function to get
the data directly?
--
David Biddulph

heather said:
TRYING TO REFENCE A CELL IN ANOTHER WORKBOOK. TO GET TO THAT I USED
=ADDRESS(MATCH(I30,'[Vendor sales at
cost.xlsx]Sheet1'!$I:$I,0),9)),0,1))
WHICH GIVES ME $J$1594
I WANT IT TO RETURN THE ACTUALL DATA THAT IS IN THAT CELL...HOW CAN I
DO
THIS? I HAVE TRIED USING =CELL("CONTENTS",FORMULAFROMABOVE) BUT THAT
RETURNS
AN ERROR...WHAT AM I DOING WRONG?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top