I am familiar with EXCEL's "named ranges" ability to name a cell
something more meaningful like "sales" rather than let EXCEL default
it to "A1".

I am working on multiple woorkbooks with multiple tabs in each. When
I use a cell from one workbook in a calcualtion in another workbook I
get something like this:

F4*K4*N4*'C:\Documents\Project A\[Workbook A.xls]'SalesTab'!$A$2

My question is, can I rename all the gooble-de-gook 'C:\Documents
\Project A\[Workbook A.xls]'SalesTab' into something more meaningful
so my calculations in the cells do not get all cluttered with path and
woorkbook file names? Or at a minium shorten the gooble-de-gook
somehow?

Thanks.

A very good question!!

Insert Name Define joesplace
refers to:
='C:\Temp\[first.xls]Sheet1'!$A$1

I was very surprised that this works!
--
Gary''s Student - gsnu200848


"quirkyjoe" wrote:

I am familiar with EXCEL's "named ranges" ability to name a cell
something more meaningful like "sales" rather than let EXCEL default
it to "A1".

I am working on multiple woorkbooks with multiple tabs in each. When
I use a cell from one workbook in a calcualtion in another workbook I
get something like this:

F4*K4*N4*'C:\Documents\Project A\[Workbook A.xls]'SalesTab'!$A$2

My question is, can I rename all the gooble-de-gook 'C:\Documents
\Project A\[Workbook A.xls]'SalesTab' into something more meaningful
so my calculations in the cells do not get all cluttered with path and
woorkbook file names? Or at a minium shorten the gooble-de-gook
somehow?

Thanks.

I don't think you're going to find a way to shorten that long
drive/path/file/tab/address string by breaking it into parts.

But you could create a name in the receiving workbook that points at that
particular range.

Then you could use:

=f4*k4*n4*ValFromSalesA2

or whatever you named that cell.

I'd open both workbooks.
In the receiving workbook
Insert|Name (xl2003 menus)
Supply a nice name and then use the mouse to point at that cell (don't type the
address yourself).

and finish up the name dialog.

=======
The problem with naming just a portion of that long string is that you'd have to
use =indirect() to combine the string to form the range. And =indirect() only
works if the sending workbook is open.

That looks like it could be a problem based on the formula you posted.

But...

(saved from a previous post)

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.



quirkyjoe wrote:

I am familiar with EXCEL's "named ranges" ability to name a cell
something more meaningful like "sales" rather than let EXCEL default
it to "A1".

I am working on multiple woorkbooks with multiple tabs in each. When
I use a cell from one workbook in a calcualtion in another workbook I
get something like this:

F4*K4*N4*'C:\Documents\Project A\[Workbook A.xls]'SalesTab'!$A$2

My question is, can I rename all the gooble-de-gook 'C:\Documents
\Project A\[Workbook A.xls]'SalesTab' into something more meaningful
so my calculations in the cells do not get all cluttered with path and
woorkbook file names? Or at a minium shorten the gooble-de-gook
somehow?

Thanks.

--

Dave Peterson

I have been using this trick in Data Validation as suggested by Debra in her
article "Data Validation -- Use a List from Another Workbook" bit it never
occurred to me to use the same like you have suggested.

"Gary''s Student" wrote:

A very good question!!

Insert Name Define joesplace
refers to:
='C:\Temp\[first.xls]Sheet1'!$A$1

I was very surprised that this works!
--
Gary''s Student - gsnu200848


"quirkyjoe" wrote:

I am familiar with EXCEL's "named ranges" ability to name a cell
something more meaningful like "sales" rather than let EXCEL default
it to "A1".

I am working on multiple woorkbooks with multiple tabs in each. When
I use a cell from one workbook in a calcualtion in another workbook I
get something like this:

F4*K4*N4*'C:\Documents\Project A\[Workbook A.xls]'SalesTab'!$A$2

My question is, can I rename all the gooble-de-gook 'C:\Documents
\Project A\[Workbook A.xls]'SalesTab' into something more meaningful
so my calculations in the cells do not get all cluttered with path and
woorkbook file names? Or at a minium shorten the gooble-de-gook
somehow?

Thanks.