Indirect function and data validation dropdown box

[Tonso]

Using XL2003, I am using the indirect function to select a named range
to display in a data validation list. At 1st it worked well, but i had
to make a change in a refernce and now i cannot get it to work. this
is what is have

in cell A1, a name. could be Tom. Sally, etc. No problem here.
in cell A3, I have a letter, could be A, B, C, etc. No problem here.
The problem is in A2. At 1st I had 1 or 2 letters, such as H, or TH.
Using the Indirect function to refernce each cell worked fine. But,
now A2 could be something like "H Go to Town", or "K Stay home", or
"TP Wash Car". I want to reference, in A2, just the 1st 1 or 2
letters. If A2 begins with "T", I need to reference the 1st 2 letters.
Otherwise, I need to reference the 1st letter only.
I tried the following:
Indirect(a1&if(a2="T",left(a2,2),left(a2,1))&a3). It doesnt work. What
am I doing wrong?
Thanks,

Tonso

 

[Don Guillett Excel MVP]

Using XL2003, I am using the indirect function to select a named range
to display in a data validation list. At 1st it worked well, but i had
to make a change in a refernce and now i cannot get it to work. this
is what is have

in cell A1, a name. could be Tom. Sally, etc.  No problem here.
in cell A3, I have a letter, could be A, B, C, etc.  No problem here.
The problem is in A2. At 1st I had 1 or 2 letters, such as H, or TH.
Using the Indirect function to refernce each cell worked fine. But,
now A2 could be something like "H Go to Town", or "K Stay home", or
"TP Wash Car". I want to reference, in A2, just the 1st 1 or 2
letters. If A2 begins with "T", I need to reference the 1st 2 letters.
Otherwise, I need to reference the 1st letter only.
I tried the following:
Indirect(a1&if(a2="T",left(a2,2),left(a2,1))&a3). It doesnt work. What
am I doing wrong?
Thanks,

Tonso

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[zvkmpw]

If A2 begins with "T", I need to reference the 1st 2 letters.

Otherwise, I need to reference the 1st letter only.
I tried the following:
Indirect(a1&if(a2="T",left(a2,2),left(a2,1))&a3). It doesnt work.

To translate 'If A2 begins with "T",...' into a formula, I'd try
IF(LEFT(A2,1)="T", ...