Concatenate w/ name of sheet

[frustratedwthis]

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

 

[JLatham]

I hope you mean information from two cells, not two complete rows. I'll go
on that premise:
A formula that will return the name of the sheet it in is this:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

To concatenate two or more values from cells you can use the CONCATENATE()
function or do it another way. I'll show both. I will also presume you wish
to have a dash (-) separating the items concatenated. That makes things a
little easier to see and understand here. You can substitute anything for
the dash including a space or just leave that part out of the concatenation
completely.

Assume that you are in cell A2 and want to concatenate A1 and B1 along with
the sheet name.
=CONCATENATE(A1,"-",B1,"-",RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))))
you can do the same thing without the CONCATENATE statement as:
=A1 & "-" & B1 & "-" &
RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

Hope that helps reduce the frustration level a little.

 

[JLatham]

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)

 

[Roger Govier]

A formula that will return the name of the sheet it in is this:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

With the proviso that file must have been saved first, for this to work.

--
Regards

Roger Govier

I hope you mean information from two cells, not two complete rows.
I'll go
on that premise:
A formula that will return the name of the sheet it in is this:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

To concatenate two or more values from cells you can use the
CONCATENATE()
function or do it another way. I'll show both. I will also presume
you wish
to have a dash (-) separating the items concatenated. That makes
things a
little easier to see and understand here. You can substitute anything
for
the dash including a space or just leave that part out of the
concatenation
completely.

Assume that you are in cell A2 and want to concatenate A1 and B1 along
with
the sheet name.
=CONCATENATE(A1,"-",B1,"-",RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))))
you can do the same thing without the CONCATENATE statement as:
=A1 & "-" & B1 & "-" &
RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

Hope that helps reduce the frustration level a little.

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

 

[frustratedwthis]

=CONCATENATE(A1,"-",B1,"-",RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))))
This worked like a charm...Thank You so much!!!

I hope you mean information from two cells, not two complete rows. I'll go
on that premise:
A formula that will return the name of the sheet it in is this:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

To concatenate two or more values from cells you can use the CONCATENATE()
function or do it another way. I'll show both. I will also presume you wish
to have a dash (-) separating the items concatenated. That makes things a
little easier to see and understand here. You can substitute anything for
the dash including a space or just leave that part out of the concatenation
completely.

Assume that you are in cell A2 and want to concatenate A1 and B1 along with
the sheet name.
=CONCATENATE(A1,"-",B1,"-",RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))))
you can do the same thing without the CONCATENATE statement as:
=A1 & "-" & B1 & "-" &
RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

Hope that helps reduce the frustration level a little.

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]

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!

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)

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

 

[JLatham]

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.

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!

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)

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