Concatenate a Worksheet Name Problem

**HangMan** · 03-08-2013, 05:45 AM

I have a main worksheet where I need to reference other worksheets in a formula, which won't be available until a date in the future. These additional sheets will all follow the same naming format, i.e. a three digit code, which I know already and have on my main sheet followed by the year, so for example the worksheet I need to reference could be called 6AE2013 or 6ET2013 and so on.

I want to set up the main sheet with all the formula in place, prior to the sheets (which are referenced in the formula) being received. I will comment all the formula out using a macro, and when they do arrive I can simply uncomment the formula and hey presto!

The problem I have is that rather than typing in each code into my formula, (there are several hundred different ones) because I know what the sheets will be called, I want to concatenate the sheet name within my formula, but I can't seem to get this to work. In Column C, I have a list of all my codes, I know my sheet name will be made from the code plus the year as shown above, but how do I concatenate this within my formula, the bits in red?

Please Login or Register  to view this content.

I tried

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

but neither work. Is it possible to do this?

Many thanks

**Pete_UK** · 03-08-2013, 05:52 AM

Try it like this:

IF(INDIRECT("'"&C1&"2013'!$C$11")="Non Lift",INDEX(INDEX(INDIRECT("'"&C1&"2013'!$1:$1000"),COLUMN(C1)+12,),ROW($C3)),"")

Hope this helps.

Pete

**HangMan** · 03-08-2013, 06:16 AM

Um!, this is not generating any error, but it isn't returning anything either. I thought maybe the " before the closing parenthesis might be in the wrong place, shouldn't it go after the 2013?, but I tried that and it then errors. Not quite sure what I'm missing?