Output sheet name and formula in a cell

[koos3r4]

Hi all

I'm quite new to VB and Excel and can't get something to work. I'm working on my fitness schedule, which consists of 16 weeks. On the first sheet (called "Main") I defined the numbers of reps and weights for each exercise per week.

For each week of training I've made a sheet that uses the numbers defined in the "Main" sheet, like so =Main!B1&Main!C1. Which outputs 3 x 8 x 70. The next week would be =Main!B2&Main!C2. The sheet for week one is named "1" and the one for week two is named "2" etc.

What I'm trying to achieve is a formula that outputs the name of the sheet in the formula "=Main!Bx&Main!Cx" (where x is the name of the sheet). So on sheet 12 automatically get the result of "=Main!B12&MainC12" in the cell B6.

Is this possible?

 

[koos3r4]

I've tried the following code in VB:

Function Benchpress()
    BenchOne = "Main!B"
    BenchTwo = "&Main!C"
    Benchpage = Range("A1").Parent.Name
    BenchCombine = BenchOne & Benchpage & BenchTwo & Benchpage
    B6.Formula = BenchCombine
End Function

Also, I'm using Excel 2013 on Windows 7
</pre>

 

[Snakehips]

koos3r4,

It's a bit of a long one but this regular formula should do it.......



=INDIRECT("Main!B"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))))&INDIRECT("Main!C"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))))



Alternatively, you could put =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))

In a celll eg A1 on each sheet which will bring up the sheet name and then reference that cell with e.g. the following formula...

=INDIRECT("Main!B"&A1)&INDIRECT("Main!C"&A1)



Hope that helps.

 

[Snakehips]

If you really want to use vba and a UDF then maybe something like.......

Excel 2007
	B	C
12	3 x 8	x 70
Main

Note that the A1 range can stay as A1 or can be any cell you like as it is only there in order to determine the sheet name /number.

Excel 2007
	C
3	3 x 8 x 70
12

Cell Formulas
Range	Formula
C3	=Benchpress(A1)

Code for the UDF needs to be in a code module......

Function Benchpress(ACell As Range) As String
MySheet = ACell.Parent.Name
With Sheets("Main")
    Benchpress = .Range("B" & MySheet) & .Range("C" & MySheet)
 End With
End Function

 

[koos3r4]

Thank you! The VB solution worked perfectly. Excel wouldn't accept the formula, so VB was the way to go on this one.