Concatenating cell values to create sheet names
I would like to concatenate cell values to create a name that corresponds to
a worksheet name and then use that in a formula. Does anyone know how to do this? |
You can use:
=a1&a2 to concatenate a couple of cells. To refer to that in another formula, you'd need something like: =indirect("'" & a1&a2 &"'!A1") or if there's a chance your concatenated string refers to a non-existing worksheet and you want to avoid a #ref! error, you can check first: =IF(ISERROR(CELL("address",INDIRECT("'"&A1&A2&"'!a 1"))),"missing", INDIRECT("'"&A1&A2&"'!a1")) (all one cell) XLDabbler wrote: I would like to concatenate cell values to create a name that corresponds to a worksheet name and then use that in a formula. Does anyone know how to do this? -- Dave Peterson |
sample:
=Sheet2!B4 So in general WORKSHEETNAME!CELL would work in formulas. "XLDabbler" wrote: I would like to concatenate cell values to create a name that corresponds to a worksheet name and then use that in a formula. Does anyone know how to do this? |
Thank you, Dave! That was exactly what I needed!
"Dave Peterson" wrote: You can use: =a1&a2 to concatenate a couple of cells. To refer to that in another formula, you'd need something like: =indirect("'" & a1&a2 &"'!A1") or if there's a chance your concatenated string refers to a non-existing worksheet and you want to avoid a #ref! error, you can check first: =IF(ISERROR(CELL("address",INDIRECT("'"&A1&A2&"'!a 1"))),"missing", INDIRECT("'"&A1&A2&"'!a1")) (all one cell) XLDabbler wrote: I would like to concatenate cell values to create a name that corresponds to a worksheet name and then use that in a formula. Does anyone know how to do this? -- Dave Peterson |
All times are GMT +1. The time now is 06:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com