Changing Cell Reference to Sheet Name via Formula

[Shrout1]

I have 6 sheets that are dynamically created. 3 are Sheet type A, 3 are sheets type B. For every type A there needs to be a Type B.

Type A references to Type B.

SheetA1 uses SheetA1:Cell A1 and references to SheetB1:Cell B1
SheetA2 uses SheetA2:Cell A1 and references to SheetB2:Cell B1
SheetA3 uses SheetA3:Cell A1 and references to SheetB3:Cell B1

Now, how do I create a formula that says, "If I'm on SheetA1, reference SheetB1:CellB1; If I'm on SheetA2, reference SheetB2:CellB1"

Thanks!

[Zack Barresse]

Hi,

=INDIRECT(LEFT(MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,LEN(CELL("filename"))-FIND("]",CELL("filename"),1)+1),5)&"B1!B1")

Does that help?

[Shrout1]

Possibly... I need to play with it...

What I'm not understanding is why the need for the Mid and the find and LEN and all...

It's funny, I've been coding in VBA for almost 2 years, but I'm still quite the newb

Thanks!

[Zack Barresse]

Ok, the formula would actually be ..

=INDIRECT(LEFT(MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,LEN(CELL("filename"))-FIND("]",CELL("filename"),1)+1),5)&"B"&RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,LEN(CELL("filename"))-FIND("]",CELL("filename"),1)+1),1)&"!B1")

This takes into account the last digit changing. You could shorten this by putting the filename (sheet) formula into another cell..

Well, the CELL("filename") formula will return the entire FullName (vba) of the workbook in which the formula is placed. To break the formula down we can look at ..

=INDIRECT(
Will evaluate the current sheet name - only B, not A - explained further..

LEFT(
This is taking the first part of the sheet name returned. (If only going be be Sheetxx, you can actually make this much shorter.) So starting from the far Left of our value, we're going x amount of spaces to the right of the value we're going to return.

MID(CELL("filename"),
Were going to go in the middle of the entire filename to retrieve the sheet name - you can also use RIGHT, it's just another way. We use the MID because you're wanting it to be broken up (basically).

FIND("]",CELL("filename"),1)+1,
Go one space to the right after the filename ([Book1.xls]), which is the first char of the sheet name.

LEN(CELL("filename"))-FIND("]",CELL("filename"),1)+1),5)
This will find the LENgth of the filename, and minus the number of characters that are prior to the sheet name, resulting in only the first 5 characters of it.

&"B"&
This will *hardcode* "B" in your sheet name, so it always refers to that sheet.

RIGHT(
This is the start of finding what sheet number you are in, e.g. SheetA1, Sheet A2, etc.

MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,LEN(CELL("filename"))-FIND("]",CELL("filename"),1)+1),1)
Same formula used above, this will be looking at the current sheet name. Only this time, we're performing the RIGHT function on it.

&"!B1")
*Hardcodes* cell B1 of said sheet into formula.

HTH

[Anne Troy]

Shrout: The mid and all that is necessary to get the sheet name. While there's a =Cell(filename) function, there's just no function to get the sheetname, dude. So we fake it out with this really long formula to grab the sheet name from what would be the filename (with the sheet name). Or something like that.

Well...I'm glad to see you're getting your money's worth here!

[Zack Barresse]

Well, you could write your own function ...

[vba]Public Function ShtName() As String
ShtName = ActiveSheet.Name
End Function
[/vba]

And then type your formula like ..

=ShtName()

[Anne Troy]

Or Zack could write it for you.

[Jacob Hilderbrand]

Hey Zack, Add your UDF to the kb. I don't think we have one that returns a sheet name yet.

[Shrout1]

Very cool - thanks a lot... I'm still workin on this one :-)

[Zack Barresse]

Shrout,

Please note that the above UDF I posted, will not always work when going across sheets; in fact it's a pretty lousy example. This has been adjusted for the following KB entry ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=213

Sorry for the confusion.

[mdmackillop]

Well this came up again, so I'll post my simpler formula here as well
=REPLACE(CELL("filename"),1,SEARCH("]",CELL("filename")),"")