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" wrote in message
...
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" wrote in message
...
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