Dave and Roger,
Thanks for jumping in with the additional info. The issues with an unsaved
workbook and even with 2 or more workbooks open I was aware of, I just didn't
think of them in time to add to my original post.

As for using the reference in the Cell() function to a cell on the worksheet
or within the workbook, I agree wholeheartedly - it should be included in the
function statement in this case. I also agree it doesn't have to be a
reference to the same cell the formula is in, but it just seemed simpler and
clearer in the example.

"Dave Peterson" wrote:

Since =cell("filename") returns the complete path, workbook name and worksheet
name, it may return the wrong worksheet name--even if there is only one workbook
open.

That function returns the name of the sheet that is active when the formula
recalculated.

You can see it by creating (and saving) a workbook with two worksheets.

Put that formula in A1 of each sheet.

Then window|New window
followed by window|arrange|horizontal.

Activate one window and recalc. And look at the results in the other window.
Activate the other window and recalc and look at the other window.

Unless you're doing something very special, you should include that range
reference in the formula.

I tend to use
=cell("Filename",a1)
but the address doesn't really matter--as long as you don't delete it!

JLatham wrote:

A little caveat I forgot to mention. The CELL("filename") formula does not
return anything until the workbook has been saved to disk. That is to say,
if you open Excel and immediately type that formula into a cell, nothing will
show up. But if you save the workbook (or open one already on disk) it will
work, although when you save a new one you may need to use [F9] to get it to
appear.

Also, it can change if you have two or more workbooks open using the format
I gave earlier. To prevent that, add the address of the cell you type the
formula into to the formula. Assuming the formula will go into cell A2,
change all references to
CELL("filename")
to
CELL("filename",A2)

"frustratedwthis" wrote:

Is there a way to concatenate 2 rows of information AND the name of the sheet?
Thank you for your help in advance!

--

Dave Peterson