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

On Jan 28, 3:56*pm, heather wrote:
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?

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" wrote:

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?

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

WHEN I DO THIS, IT JUST RETURNS AN ERROR MESSAGE SAYING SOMETHING IN MY
FORMULA IS WORNG...

"Shane Devenshire" wrote:

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" wrote:

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?

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" wrote:

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

Look in Excel help for VLOOKUP.
--
David Biddulph

"heather" wrote in message
...
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" wrote:

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