Converting "ADDRESS" Results to data referanced

[Mike Quinn, SrA, USAF]

I am trying to make a formula to referance external data with the ability to
adapt to adding in columns in the external file. I have got the address
function to give me the correct coordinates for the information required,
however, I want it to display the information in the cell I am referancing
and not the actual coordinates.

Formula:
=ADDRESS(333,LOOKUP(A4,'[external file
name]sheet1!'!B$1:$CC$1,COLUMN(1:81)+1),1,1,"#external file name#")

Actual Result:
'[external file name]sheet1!'!$D$333

Desired Results (i.e.):
"80%"

 

[Bob Phillips]

IF the workbook is open, you could INDIRECT that result.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<Mike Quinn>; <SrA>; "USAF" <Mike Quinn, SrA,
(e-mail address removed)> wrote in message
news:[email protected]...

 

[hrlngrv]

Mike wrote...

I am trying to make a formula to referance external data with the ability to
adapt to adding in columns in the external file. I have got the address
function to give me the correct coordinates for the information required,
however, I want it to display the information in the cell I am referancing
and not the actual coordinates.

Formula:
=ADDRESS(333,LOOKUP(A4,'[external file name]sheet1!'!B$1:$CC$1,
COLUMN(1:81)+1),1,1,"#external file name#")

Actual Result:
'[external file name]sheet1!'!$D$333

Desired Results (i.e.):
"80%"

You'd need to wrap this inside INDIRECT, but this only works when the
other workbooks are open. Also, there's no good reason to use ADDRESS
for this.

INDIRECT(ADDRESS(333,x,1,1,y))

could (& should) be replaced by

INDIRECT("'"&y&"'!R333C"&x,0))

However, if the tokens '[external file name]sheet1!' and "#external
file name#" would always refer to the same workbook and worksheet, then
you should use

HLOOKUP(A4,'[external file name]sheet1!'!B$1:$CC$333,333)

or

LOOKUP(A4,'[external file name]sheet1!'!B$1:$CC$1,
'[external file name]sheet1!'!B$333:$CC$333)

These formulas use constant, literal external references, which Excel
can & does handle when those workbooks are closed.

 

[Mike Quinn, SrA, USAF]

Thank You so very much, you just eliminated about 3 hours worth of work for
me every week.

Mike wrote...

I am trying to make a formula to referance external data with the ability to
adapt to adding in columns in the external file. I have got the address
function to give me the correct coordinates for the information required,
however, I want it to display the information in the cell I am referancing
and not the actual coordinates.

Formula:
=ADDRESS(333,LOOKUP(A4,'[external file name]sheet1!'!B$1:$CC$1,
COLUMN(1:81)+1),1,1,"#external file name#")

Actual Result:
'[external file name]sheet1!'!$D$333

Desired Results (i.e.):
"80%"

You'd need to wrap this inside INDIRECT, but this only works when the
other workbooks are open. Also, there's no good reason to use ADDRESS
for this.

INDIRECT(ADDRESS(333,x,1,1,y))

could (& should) be replaced by

INDIRECT("'"&y&"'!R333C"&x,0))

However, if the tokens '[external file name]sheet1!' and "#external
file name#" would always refer to the same workbook and worksheet, then
you should use

HLOOKUP(A4,'[external file name]sheet1!'!B$1:$CC$333,333)

or

LOOKUP(A4,'[external file name]sheet1!'!B$1:$CC$1,
'[external file name]sheet1!'!B$333:$CC$333)

These formulas use constant, literal external references, which Excel
can & does handle when those workbooks are closed.