ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Concatenating cell values to create sheet names (https://www.excelbanter.com/excel-discussion-misc-queries/42833-concatenating-cell-values-create-sheet-names.html)

XLDabbler August 29th 05 06:09 PM

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?

Dave Peterson August 29th 05 07:06 PM

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

Mike August 29th 05 09:47 PM

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?


XLDabbler August 29th 05 09:55 PM

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 12:11 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
ExcelBanter.com