OFFSET and external workbook references

[Dave F]

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.

 

[RagDyeR]

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!
=====================================================


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.

 

[Peo Sjoblom]

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]

Thanks to both; these solutions work perfectly.

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

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 -