Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable for a worksheet name in a formula
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! -- caroline |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable for a worksheet name in a formula
Try something like this
=INDIRECT("'" & A1 & "'!B1",TRUE) Where A1 contains the Worksheet Name. -- HTH, Barb Reinhardt "caroline" wrote: 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! -- caroline |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable for a worksheet name in a formula
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 -- Gary "caroline" wrote in message ... 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! -- caroline |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a variable for a worksheet name in a formula
On Sep 13, 8:26 am, Barb Reinhardt
wrote: Try something like this =INDIRECT("'" & A1 & "'!B1",TRUE) Where A1 contains the Worksheet Name. -- HTH, Barb Reinhardt "caroline" wrote: Hello, I would like to use aformulawith 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 theformula. My worksheets are named 'Input','Definition' . . . I have found thisformulain previous answers =INDIRECT("'sheet (" & A1 & ")'!B1") but this works only if the worksheets are named 'Sheet (1)','Sheet (2)' Any idea? Thanks! -- caroline- Hide quoted text - - 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Worksheet Name in Formula | Excel Worksheet Functions | |||
How do I use a worksheet name as an input variable to a formula? | Excel Discussion (Misc queries) | |||
Reference Worksheet Name variable in VBA formula | Excel Programming | |||
Formula Referencing Worksheet Name by Variable | Excel Programming | |||
Formula Referencing Worksheet Name by Variable | Excel Programming |