OFFSET and external workbook references

D

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.
 
R

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.
 
P

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
 
D

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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top