OFFSET and external workbook references
 

I have a bunch of formulas that use OFFSET, such as:
=OFFSET(INDEX('[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,MATCH(B2,'[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,0)),,-2)

As you can see, this references an external workbook. When the
external workbook is not open, this generates #VALUE! errors. I
understand that some functions do not work when they reference a
closed, external workbook.

I don't want to put the table in the workbook because of its size. Is
there a way I can modify the above formula to avoid using the OFFSET
function (which I am assuming causes the #VALUE! error)?

Thanks.

OFFSET and external workbook references
 

Use a "straight" Index() formula:

=INDEX('[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$C$1:$C$1050,MATCH(B2,'[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Dave F" wrote in message
ups.com...
I have a bunch of formulas that use OFFSET, such as:
=OFFSET(INDEX('[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,MATCH(B2,'[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,0)),,-2)

As you can see, this references an external workbook. When the
external workbook is not open, this generates #VALUE! errors. I
understand that some functions do not work when they reference a
closed, external workbook.

I don't want to put the table in the workbook because of its size. Is
there a way I can modify the above formula to avoid using the OFFSET
function (which I am assuming causes the #VALUE! error)?

Thanks.

OFFSET and external workbook references
 

This will work

=INDEX('[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$A$1:$E$1050,MATCH(B2,'[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0),3)


or this


=INDEX('[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$C$1:$C$1050,MATCH(B2,'[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0))


I don't see the need for OFFSET at all


--


Regards,


Peo Sjoblom


"Dave F" wrote in message
ups.com...
I have a bunch of formulas that use OFFSET, such as:
=OFFSET(INDEX('[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,MATCH(B2,'[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,0)),,-2)

As you can see, this references an external workbook. When the
external workbook is not open, this generates #VALUE! errors. I
understand that some functions do not work when they reference a
closed, external workbook.

I don't want to put the table in the workbook because of its size. Is
there a way I can modify the above formula to avoid using the OFFSET
function (which I am assuming causes the #VALUE! error)?

Thanks.

OFFSET and external workbook references
 

Thanks to both; these solutions work perfectly.

On Oct 25, 11:14 am, "Peo Sjoblom" wrote:
This will work

=INDEX('[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$A$1:$E$1050,MATCH(B2,'[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0),3)

or this

=INDEX('[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$C$1:$C$1050,MATCH(B2,'[Copy of MAPPING
TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E$1050,0))

I don't see the need for OFFSET at all

--

Regards,

Peo Sjoblom

"Dave F" wrote in message

ups.com...



I have a bunch of formulas that use OFFSET, such as:
=OFFSET(INDEX('[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,MATCH(B2,'[Copy of MAPPING TABLE.xls]MAPPING_ACCOUNTS'!$E$1:$E
$1050,0)),,-2)

As you can see, this references an external workbook. When the
external workbook is not open, this generates #VALUE! errors. I
understand that some functions do not work when they reference a
closed, external workbook.

I don't want to put the table in the workbook because of its size. Is
there a way I can modify the above formula to avoid using the OFFSET
function (which I am assuming causes the #VALUE! error)?

Thanks.- Hide quoted text -

- Show quoted text -