use a variable for a worksheet name in a formula

[Guest]

Hello,
I would like to use a formula with variable to access worksheets
(Like the VBA Worksheets(Range("a1").Value). I do not want to use VBA
because I need the user to see the formula.

My worksheets are named 'Input','Definition' . . .
I have found this formula in previous answers
=INDIRECT("'sheet (" & A1 & ")'!B1")
but this works only if the worksheets are named 'Sheet (1)','Sheet (2)'

Any idea?
Thanks!

 

[Guest]

Try something like this

=INDIRECT("'" & A1 & "'!B1",TRUE)

Where A1 contains the Worksheet Name.

 

[Gary Keramidas]

you could use vb and use something like this:

Sub test()
Dim ws As Worksheet
Set ws = Worksheets(1)
Range("B2").Formula = "=INDIRECT(""" & "'" & ws.Name & "'!B1""" & ")"
End Sub

 

[jfrick100]

Try something like this

=INDIRECT("'" & A1 & "'!B1",TRUE)

Where A1 contains the Worksheet Name.
--
HTH,
Barb Reinhardt







- Show quoted text -

I have a similar problem that I just posted. I found this message
about INDIRECT after my posting. I thought it would work, but I need
a little more help.

Here is why: the INDIRECT needs to have a cell reference, like A1 in
the above posting. It almost does what I need, but to us eINDIRECT I
would have to create a cell that has the value I need. In fact I have
so many values that create an equivalent cell for each value would be
a poblem.

Instead I need something like the INDIRECT that lets me put formulas
into the spot that A1 shows above. For example I need something like
this:

=INDIRECT("'" & "[Source Data.xls]" & TEXT(YEAR(A381),"0000") &
TEXT(MONTH(A381),"00") & "'!$J$6",TRUE)

(Note: this is what I am looking for. It is not the correct use of
INDIRECT.)

It would seem simple just to stuff a cell with the formula "[Source
Data.xls]" & TEXT(YEAR(A381),"0000") & TEXT(MONTH(A381),"00"), but I
have so many of these to do that it would take forever to build the A1
type cells.

Is there a way to do this?