Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate w/ name of sheet
Is there a way to concatenate 2 rows of information AND the name of the sheet?
Thank you for your help in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate w/ name of sheet
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. "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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate w/ name of sheet
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate w/ name of sheet
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 "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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. "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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate w/ name of sheet
=CONCATENATE(A1,"-",B1,"-",RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))))
This worked like a charm...Thank You so much!!! "JLatham" wrote: 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. "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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate w/ name of sheet
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate w/ name of sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Sum up columns in different sheet with error check | Excel Discussion (Misc queries) | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions |