Thread: Worksheet name syntax question

greetings

I have code that generates names (eg T2_Master)
Dim TaskWSName as String
TaskWSName = "T" & ActiveSheet.Range(E8) & "_Master"

Later, I want to have code that refers to Worksheets having these names.
Here's an eg:
Worksheets("PrintReport").Range("A1").Formula =
"=INDEX(T2_Master!E:E,MATCH(A8,T2_Master!A:A,0))


How do I refer to T2_Master! in the INDEX or the MATCH statements? Using
TaskWSName! doesn't work.

Thanks in advance
anny

I would let excel do the work for me:

dim ColE as range
dim ColA as range
dim TaskWSName as String

'watch out for "E8" <--with double quotes
TaskWSName = "T" & ActiveSheet.Range("E8").value & "_Master"

with worksheets(taskwsname)
set cole=.range("e:E")
set colA = .range("a:a")
end with

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & cole.address(external:=true) & ",match(a8," & _
cola.address(external:=true) & ",0))"


======
You could build the strings yourself,

Worksheets("PrintReport").Range("A1").Formula _
= "=index('" & TaskWSName & "'!a:a,match(a8," & _
"'" & TaskWSName & "'!a:a,0))"

===
You could drop the $ signs by using:

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & ColE.Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) & ",match(a8," & _
ColA.Address(rowabsolute:=False, _
columnabsolute:=False, external:=True) & ",0))"

anny wrote:
>
> greetings
>
> I have code that generates names (eg T2_Master)
> Dim TaskWSName as String
> TaskWSName = "T" & ActiveSheet.Range(E8) & "_Master"
>
> Later, I want to have code that refers to Worksheets having these names.
> Here's an eg:
> Worksheets("PrintReport").Range("A1").Formula =
> "=INDEX(T2_Master!E:E,MATCH(A8,T2_Master!A:A,0))
>
> How do I refer to T2_Master! in the INDEX or the MATCH statements? Using
> TaskWSName! doesn't work.
>
> Thanks in advance
> anny

--

Dave Peterson

Thank you! makes lots of sense and provides me with multi-options
anny

Dave : I'm a bit puzzled about your use of apostrophes with the quotation
marks (in 4 places)

> You could build the strings yourself,
>
> Worksheets("PrintReport").Range("A1").Formula _
> = "=index('" & TaskWSName & "'!a:a,match(a8," & _
> "'" & TaskWSName & "'!a:a,0))"

Why can't I just do the following? It seems to work OK. Do I really need
the aposstrophes?

Worksheets("PrintReport").Range("A1").Formula _
= "=index(" & TaskWSName & "!a:a,match(a8," & _
TaskWSName & "!a:a,0))"

Thanks again
anny



"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43EA2FC3.5BEF083@verizonXSPAM.net...
>I would let excel do the work for me:
>
> dim ColE as range
> dim ColA as range
> dim TaskWSName as String
>
> 'watch out for "E8" <--with double quotes
> TaskWSName = "T" & ActiveSheet.Range("E8").value & "_Master"
>
> with worksheets(taskwsname)
> set cole=.range("e:E")
> set colA = .range("a:a")
> end with
>
> Worksheets("PrintReport").Range("A1").Formula _
> = "=index(" & cole.address(external:=true) & ",match(a8," & _
> cola.address(external:=true) & ",0))"
>
>
> ======
> You could build the strings yourself,
>
> Worksheets("PrintReport").Range("A1").Formula _
> = "=index('" & TaskWSName & "'!a:a,match(a8," & _
> "'" & TaskWSName & "'!a:a,0))"
>
> ===
> You could drop the $ signs by using:
>
> Worksheets("PrintReport").Range("A1").Formula _
> = "=index(" & ColE.Address(rowabsolute:=False, _
> columnabsolute:=False, external:=True) & ",match(a8," & _
> ColA.Address(rowabsolute:=False, _
> columnabsolute:=False, external:=True) & ",0))"
>
> anny wrote:
>>
>> greetings
>>
>> I have code that generates names (eg T2_Master)
>> Dim TaskWSName as String
>> TaskWSName = "T" & ActiveSheet.Range(E8) & "_Master"
>>
>> Later, I want to have code that refers to Worksheets having these names.
>> Here's an eg:
>> Worksheets("PrintReport").Range("A1").Formula =
>> "=INDEX(T2_Master!E:E,MATCH(A8,T2_Master!A:A,0))
>>
>> How do I refer to T2_Master! in the INDEX or the MATCH statements?
>> Using
>> TaskWSName! doesn't work.
>>
>> Thanks in advance
>> anny
>
> --
>
> Dave Peterson

It depends on what's in TaskWSName.

Try a simple test.

Create a workbook with two sheets. Name them A and B.
In A1 of worksheet A, put this formula:
=b!a1

Now rename worksheet B to "this is worksheet B"
and take a look at your formula.

So if you always know that the sheetname will be T2_Master, those apostrophes
aren't required. But they don't hurt.

And if the sheet name changes to where the apostrophes are required, you have to
have them (well, that's what required means <bg>.)

So I think it's a good thing to add them all the time. It saves wear and tear
when something else changes.

anny wrote:
>
> Dave : I'm a bit puzzled about your use of apostrophes with the quotation
> marks (in 4 places)
>
> > You could build the strings yourself,
> >
> > Worksheets("PrintReport").Range("A1").Formula _
> > = "=index('" & TaskWSName & "'!a:a,match(a8," & _
> > "'" & TaskWSName & "'!a:a,0))"
>
> Why can't I just do the following? It seems to work OK. Do I really need
> the aposstrophes?
>
> Worksheets("PrintReport").Range("A1").Formula _
> = "=index(" & TaskWSName & "!a:a,match(a8," & _
> TaskWSName & "!a:a,0))"
>
> Thanks again
> anny
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43EA2FC3.5BEF083@verizonXSPAM.net...
> >I would let excel do the work for me:
> >
> > dim ColE as range
> > dim ColA as range
> > dim TaskWSName as String
> >
> > 'watch out for "E8" <--with double quotes
> > TaskWSName = "T" & ActiveSheet.Range("E8").value & "_Master"
> >
> > with worksheets(taskwsname)
> > set cole=.range("e:E")
> > set colA = .range("a:a")
> > end with
> >
> > Worksheets("PrintReport").Range("A1").Formula _
> > = "=index(" & cole.address(external:=true) & ",match(a8," & _
> > cola.address(external:=true) & ",0))"
> >
> >
> > ======
> > You could build the strings yourself,
> >
> > Worksheets("PrintReport").Range("A1").Formula _
> > = "=index('" & TaskWSName & "'!a:a,match(a8," & _
> > "'" & TaskWSName & "'!a:a,0))"
> >
> > ===
> > You could drop the $ signs by using:
> >
> > Worksheets("PrintReport").Range("A1").Formula _
> > = "=index(" & ColE.Address(rowabsolute:=False, _
> > columnabsolute:=False, external:=True) & ",match(a8," & _
> > ColA.Address(rowabsolute:=False, _
> > columnabsolute:=False, external:=True) & ",0))"
> >
> > anny wrote:
> >>
> >> greetings
> >>
> >> I have code that generates names (eg T2_Master)
> >> Dim TaskWSName as String
> >> TaskWSName = "T" & ActiveSheet.Range(E8) & "_Master"
> >>
> >> Later, I want to have code that refers to Worksheets having these names.
> >> Here's an eg:
> >> Worksheets("PrintReport").Range("A1").Formula =
> >> "=INDEX(T2_Master!E:E,MATCH(A8,T2_Master!A:A,0))
> >>
> >> How do I refer to T2_Master! in the INDEX or the MATCH statements?
> >> Using
> >> TaskWSName! doesn't work.
> >>
> >> Thanks in advance
> >> anny
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson