reference workbook name in cell

  • Thread starter Thread starter Dave Potter
  • Start date Start date
D

Dave Potter

Given: XL2002 Win XP

I have a workbook name DTS-351-Jan.xls and I would like to have cell
N3 in worksheet Main extract the 351 from the wb name so that N3 would
say 351. Is it possible and if so how? I will have other workbooks
that i would like to use the same formula in so it would be important
that the formula not conatain an absolute reference to 351.
Thanks,
Dave
 
Given: XL2002 Win XP

I have a workbook name DTS-351-Jan.xls and I would like to have cell
N3 in worksheet Main extract the 351 from the wb name so that N3 would
say 351. Is it possible and if so how? I will have other workbooks
that i would like to use the same formula in so it would be important
that the formula not conatain an absolute reference to 351.
Thanks,
Dave
Click to expand...

You can get the full file name, including path, by entering:
=CELL("filename",A1)
in a cell. The file name will be contained within square brackets, so
it should be easy enough to parse out using the MID and FIND
functions. As for extracting the string 351, though... you've given
WAY too little information. We don't know whether the file names will
ALWAYS follow the DTS-xxx-MMM.xls format that you have above, or
whether there could be other file name formats, or whether the number
will always have three digits...

If the file names are too "random" in their structure, it may be
better to go for a VBA user defined function. If they're fairly
constant, it should be doable with the CELL function and the right
combination of text functions.

If you can't figure out the solution given the information above, post
again with specifics of the possible file names.
 
Hank,
The file names will always follow the same naming convention.
DTS-351-Jan
DTS-351-Feb
DTS-352-Mar
DTS-364-Apr
etc...
I will have to study the MID and FIND functions.
Thanks,
Dave
 
Hank,
When I tried the =CELL("filename",A1) formula it returned the path and
filename.
 
On Sun, 10 Aug 2003 00:02:15 -0400, Dave Potter

Yes, that's what I mentioned in the original message. However the file
name itself should be in square brackets, so the formula:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+5,3)

should work. To explain it:
The Mid function asks you to specify:
- The text that you want to extract something from; in this case, the
Cell function;
- The starting position (which we identify with the FIND function
described below); and
- how many characters you want to extract. (In this case, 3.)

To find the starting position, we use the FIND function. That takes
the following arguments:
- The text that you want to find (in this case the opening square
bracket from the Cell function); and
- The text that you want to find it in (the cell function's value)

We add 5 to that value because we know that the number that you're
after always starts at the 5th character of the file name.
 
Thanks Hank. It worked great!
Dave


On Sun, 10 Aug 2003 00:02:15 -0400, Dave Potter

Yes, that's what I mentioned in the original message. However the file
name itself should be in square brackets, so the formula:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+5,3)

should work. To explain it:
The Mid function asks you to specify:
- The text that you want to extract something from; in this case, the
Cell function;
- The starting position (which we identify with the FIND function
described below); and
- how many characters you want to extract. (In this case, 3.)

To find the starting position, we use the FIND function. That takes
the following arguments:
- The text that you want to find (in this case the opening square
bracket from the Cell function); and
- The text that you want to find it in (the cell function's value)

We add 5 to that value because we know that the number that you're
after always starts at the 5th character of the file name.
Hank,
When I tried the =CELL("filename",A1) formula it returned the path and
filename.
Click to expand...
Click to expand...
 
Hank Scorpio said:
You can get the full file name, including path, by entering:
=CELL("filename",A1)
Click to expand...

Just out of interest, does the A1 make any difference? Works the same with
or without in Excel 97, and I have always left the A1 out. Is it more
'compatible' to use the A1?

Geoff
 
Try this to see:

Open two existing workbooks.
Window|Arrange|horizontal
Make it so you can see one sheet from each workbook.
put =cell("filename")
in a1 of both sheets.

click on the bottom sheet and hit F9. Look at the results of both formulas.
That's the problem if you don't put a reference in the formula.
 
Why? dunno. But I'm glad it does work this way.

But it gives a lot more options to the user than if it only used the
activesheet.

From the help for the worksheet function =cell()

Reference is the cell that you want information about. If omitted, information
specified in info_type is returned for the last cell that was changed.
 
Back
Top