use a variable for a worksheet name in a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

caroline - 13 Sep 2007 13:04 GMT

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!

Signature

caroline

Reply to this Message

Barb Reinhardt - 13 Sep 2007 13:26 GMT

Try something like this

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

Where A1 contains the Worksheet Name.

Signature

HTH,
Barb Reinhardt

> Hello,
> I would like to use a formula with variable to access worksheets
[quoted text clipped - 8 lines]
> Any idea?
> Thanks!

Reply to this Message

jfrick100@gmail.com - 28 Sep 2007 19:40 GMT

On Sep 13, 8:26 am, Barb Reinhardt
<BarbReinha...@discussions.microsoft.com> wrote:

> Try something like this
>
[quoted text clipped - 22 lines]
>
> - 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?

Reply to this Message

Gary Keramidas - 13 Sep 2007 13:36 GMT

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

Signature

Gary

> Hello,
> I would like to use a formula with variable to access worksheets
[quoted text clipped - 8 lines]
> Any idea?
> Thanks!

Reply to this Message