Name in cell ref used in a formula

P

Phendrena

Hello,

I have the following formula (this is only a part of the formula) :

=SUMPRODUCT(--(MONTH('S:\BTeams\Affinity\Affinity\Automotive Ops
Database\MTC Capture Tool 2\Fred''s Team\[MTC - Joe
Bloggs.xls]Mid-Term'!$B$3:$B$503)=12)

I have Joe Bloggs as a name in the formula and also in a cell.
Can i use the cell referrence, such as C6 instead of the name? Would this
then referrence the external spreadsheet correctly or am i stuck using the
full name?
So it would read [MTC Tool - "Cell Ref".xls]

Thanks,
 
T

T. Valko

Would this then referrence the external spreadsheet correctly or am i stuck
using the full name?

There is a way to do it the way you want with built-in functions *BUT* the
referenced file *MUST* be open. This is usually a "deal breaker"!

There are a couple of other methods that might work on the closed file but
these require VBA routines and one is an add-in. Still interested?


--
Biff
Microsoft Excel MVP


Phendrena said:
Hello,

I have the following formula (this is only a part of the formula) :

=SUMPRODUCT(--(MONTH('S:\BTeams\Affinity\Affinity\Automotive Ops
Database\MTC Capture Tool 2\Fred''s Team\[MTC - Joe
Bloggs.xls]Mid-Term'!$B$3:$B$503)=12)

I have Joe Bloggs as a name in the formula and also in a cell.
Can i use the cell referrence, such as C6 instead of the name? Would this
then referrence the external spreadsheet correctly or am i stuck using the
full name?
So it would read [MTC Tool - "Cell Ref".xls]

Thanks,
 
P

Phendrena

As long as i can get Excel do what i want it do i'm interested!

thanks


T. Valko said:
Would this then referrence the external spreadsheet correctly or am i stuck
using the full name?

There is a way to do it the way you want with built-in functions *BUT* the
referenced file *MUST* be open. This is usually a "deal breaker"!

There are a couple of other methods that might work on the closed file but
these require VBA routines and one is an add-in. Still interested?


--
Biff
Microsoft Excel MVP


Phendrena said:
Hello,

I have the following formula (this is only a part of the formula) :

=SUMPRODUCT(--(MONTH('S:\BTeams\Affinity\Affinity\Automotive Ops
Database\MTC Capture Tool 2\Fred''s Team\[MTC - Joe
Bloggs.xls]Mid-Term'!$B$3:$B$503)=12)

I have Joe Bloggs as a name in the formula and also in a cell.
Can i use the cell referrence, such as C6 instead of the name? Would this
then referrence the external spreadsheet correctly or am i stuck using the
full name?
So it would read [MTC Tool - "Cell Ref".xls]

Thanks,
 
T

T. Valko

There is an add-in available here:

http://xcell05.free.fr/english/

Look for Morefunc.xll

This has a function called INDIRECT.EXT that will do what you want. See the
add-in help on its use.

--
Biff
Microsoft Excel MVP


Phendrena said:
As long as i can get Excel do what i want it do i'm interested!

thanks


T. Valko said:
Would this then referrence the external spreadsheet correctly or am i
stuck
using the full name?

There is a way to do it the way you want with built-in functions *BUT*
the
referenced file *MUST* be open. This is usually a "deal breaker"!

There are a couple of other methods that might work on the closed file
but
these require VBA routines and one is an add-in. Still interested?


--
Biff
Microsoft Excel MVP


Phendrena said:
Hello,

I have the following formula (this is only a part of the formula) :

=SUMPRODUCT(--(MONTH('S:\BTeams\Affinity\Affinity\Automotive Ops
Database\MTC Capture Tool 2\Fred''s Team\[MTC - Joe
Bloggs.xls]Mid-Term'!$B$3:$B$503)=12)

I have Joe Bloggs as a name in the formula and also in a cell.
Can i use the cell referrence, such as C6 instead of the name? Would
this
then referrence the external spreadsheet correctly or am i stuck using
the
full name?
So it would read [MTC Tool - "Cell Ref".xls]

Thanks,
 
P

Phendrena

Thanks for the reply, there are some interesting functions in that add-in!

T. Valko said:
There is an add-in available here:

http://xcell05.free.fr/english/

Look for Morefunc.xll

This has a function called INDIRECT.EXT that will do what you want. See the
add-in help on its use.

--
Biff
Microsoft Excel MVP


Phendrena said:
As long as i can get Excel do what i want it do i'm interested!

thanks


T. Valko said:
Would this then referrence the external spreadsheet correctly or am i
stuck
using the full name?

There is a way to do it the way you want with built-in functions *BUT*
the
referenced file *MUST* be open. This is usually a "deal breaker"!

There are a couple of other methods that might work on the closed file
but
these require VBA routines and one is an add-in. Still interested?


--
Biff
Microsoft Excel MVP


Hello,

I have the following formula (this is only a part of the formula) :

=SUMPRODUCT(--(MONTH('S:\BTeams\Affinity\Affinity\Automotive Ops
Database\MTC Capture Tool 2\Fred''s Team\[MTC - Joe
Bloggs.xls]Mid-Term'!$B$3:$B$503)=12)

I have Joe Bloggs as a name in the formula and also in a cell.
Can i use the cell referrence, such as C6 instead of the name? Would
this
then referrence the external spreadsheet correctly or am i stuck using
the
full name?
So it would read [MTC Tool - "Cell Ref".xls]

Thanks,
 
T

T. Valko

Yeah, it's probably one of the most popular/useful add-ins out there.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Phendrena said:
Thanks for the reply, there are some interesting functions in that add-in!

T. Valko said:
There is an add-in available here:

http://xcell05.free.fr/english/

Look for Morefunc.xll

This has a function called INDIRECT.EXT that will do what you want. See
the
add-in help on its use.

--
Biff
Microsoft Excel MVP


Phendrena said:
As long as i can get Excel do what i want it do i'm interested!

thanks


:

Would this then referrence the external spreadsheet correctly or am i
stuck
using the full name?

There is a way to do it the way you want with built-in functions *BUT*
the
referenced file *MUST* be open. This is usually a "deal breaker"!

There are a couple of other methods that might work on the closed file
but
these require VBA routines and one is an add-in. Still interested?


--
Biff
Microsoft Excel MVP


Hello,

I have the following formula (this is only a part of the formula) :

=SUMPRODUCT(--(MONTH('S:\BTeams\Affinity\Affinity\Automotive Ops
Database\MTC Capture Tool 2\Fred''s Team\[MTC - Joe
Bloggs.xls]Mid-Term'!$B$3:$B$503)=12)

I have Joe Bloggs as a name in the formula and also in a cell.
Can i use the cell referrence, such as C6 instead of the name? Would
this
then referrence the external spreadsheet correctly or am i stuck
using
the
full name?
So it would read [MTC Tool - "Cell Ref".xls]

Thanks,
 

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