Here's the dilemma:

In each cell of column 1of worksheet 1, I want a formula of the form:

='target worksheet tabname'!A1

In the cells of column 2 of worksheet 1, I have 'target worksheet
tabname' entries.

How would I embed the text from the column 2 cells into the formula of
the column 1 cells?

I tried concatenating parts of the formula (both with 'concatenate'
and ampersand option) but couldn't make it work. (concatenate
appeared to work but to make it return the result from the target
worksheet, I had to enter the cursor in the formula cell and hit
enter)

Thanks,

Jeff

I'm not sure I understand what you mean, but if you've got worksheet
names in column 2, then you can use

=INDIRECT(B1 & "!A1")

(assuming that the single quotes are in B1. If not:

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

In article <(E-Mail Removed)>,
Miner Jeff <(E-Mail Removed)> wrote:

> Here's the dilemma:
>
> In each cell of column 1of worksheet 1, I want a formula of the form:
>
> ='target worksheet tabname'!A1
>
> In the cells of column 2 of worksheet 1, I have 'target worksheet
> tabname' entries.
>
> How would I embed the text from the column 2 cells into the formula of
> the column 1 cells?
>
> I tried concatenating parts of the formula (both with 'concatenate'
> and ampersand option) but couldn't make it work. (concatenate
> appeared to work but to make it return the result from the target
> worksheet, I had to enter the cursor in the formula cell and hit
> enter)
>
> Thanks,
>
> Jeff

On Jun 11, 5:05 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> I'm not sure I understand what you mean, but if you've got worksheet
> names in column 2, then you can use
>
> =INDIRECT(B1 & "!A1")
>
> (assuming that the single quotes are in B1. If not:
>
> =INDIRECT("'" & B1 & "'!A1")
>
> In article <1181599065.916513.200...@g4g2000hsf.googlegroups.com>,
> MinerJeff <jeff.p.bled...@lmco.com> wrote:
>
>
>
> > Here's the dilemma:
>
> > In each cell of column 1of worksheet 1, I want a formula of the form:
>
> > ='target worksheet tabname'!A1
>
> > In the cells of column 2 of worksheet 1, I have 'target worksheet
> > tabname' entries.
>
> > How would I embed the text from the column 2 cells into the formula of
> > the column 1 cells?
>
> > I tried concatenating parts of the formula (both with 'concatenate'
> > and ampersand option) but couldn't make it work. (concatenate
> > appeared to work but to make it return the result from the target
> > worksheet, I had to enter the cursor in the formula cell and hit
> > enter)
>
> > Thanks,
>
> > Jeff- Hide quoted text -
>
> - Show quoted text -

JE,

Thanks for the help. Your first suggestion worked. I was hoping the
solution would solve my 'bigger' problem but now I'm not so sure.

Here's the real problem I'm trying to solve:

In the formula,

=IF(AND($D402="INPUT",
$H402="INTERLOCK")=TRUE,VLOOKUP($W402,'1260-17A_SLOT_1'!$A$2:$F
$81,2,FALSE)&"-"&VLOOKUP($W402,'1260-17A_SLOT_1'!$A$2:$F
$81,5,FALSE),IF(AND($D402="INPUT",$J402="")=TRUE,VLOOKUP($I402,$B$2:$M
$5000,12,FALSE),IF(AND(LEFT($L402,10)="DTS E1459A",
$J402="")=TRUE,VLOOKUP($I402,$B$2:$M$5000,12,FALSE),"")))

do you know how can I use your INDIRECT solution to refer to
'1260-17A_SLOT_1', where '1260-17A_SLOT_1' is contained in a cell
adjacent to the formula's cell, or at least on the same row ?

Thanks again,

Jeff

One way:

=IF(AND($D402="INPUT", $H402="INTERLOCK")=TRUE,
VLOOKUP($W402,INDIRECT("'" & X & "'!A2:F81"), 2, FALSE) & "-" &
VLOOKUP($W402,INDIRECT("'" & X & "'!A2:F81"), 5, FALSE),
IF(AND($D402="INPUT", $J402="")=TRUE, VLOOKUP($I402, $B$2:$M$5000, 12,
FALSE), IF(AND(LEFT($L402, 10)="DTS E1459A", $J402="")=TRUE,
VLOOKUP($I402, $B$2:$M$5000, 12, FALSE), "")))

Where X is your adjacent cell

In article <(E-Mail Removed)>,
Miner Jeff <(E-Mail Removed)> wrote:

> Here's the real problem I'm trying to solve:
>
> In the formula,
>
> =IF(AND($D402="INPUT",
> $H402="INTERLOCK")=TRUE,VLOOKUP($W402,'1260-17A_SLOT_1'!$A$2:$F
> $81,2,FALSE)&"-"&VLOOKUP($W402,'1260-17A_SLOT_1'!$A$2:$F
> $81,5,FALSE),IF(AND($D402="INPUT",$J402="")=TRUE,VLOOKUP($I402,$B$2:$M
> $5000,12,FALSE),IF(AND(LEFT($L402,10)="DTS E1459A",
> $J402="")=TRUE,VLOOKUP($I402,$B$2:$M$5000,12,FALSE),"")))
>
> do you know how can I use your INDIRECT solution to refer to
> '1260-17A_SLOT_1', where '1260-17A_SLOT_1' is contained in a cell
> adjacent to the formula's cell, or at least on the same row ?

On Jun 13, 12:30 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> One way:
>
> =IF(AND($D402="INPUT", $H402="INTERLOCK")=TRUE,
> VLOOKUP($W402,INDIRECT("'" & X & "'!A2:F81"), 2, FALSE) & "-" &
> VLOOKUP($W402,INDIRECT("'" & X & "'!A2:F81"), 5, FALSE),
> IF(AND($D402="INPUT", $J402="")=TRUE, VLOOKUP($I402, $B$2:$M$5000, 12,
> FALSE), IF(AND(LEFT($L402, 10)="DTS E1459A", $J402="")=TRUE,
> VLOOKUP($I402, $B$2:$M$5000, 12, FALSE), "")))
>
> Where X is your adjacent cell
>
> In article <1181744599.996959.57...@g37g2000prf.googlegroups.com>,
> Miner Jeff <jeff.p.bled...@lmco.com> wrote:
>
>
>
> > Here's the real problem I'm trying to solve:
>
> > In the formula,
>
> > =IF(AND($D402="INPUT",
> > $H402="INTERLOCK")=TRUE,VLOOKUP($W402,'1260-17A_SLOT_1'!$A$2:$F
> > $81,2,FALSE)&"-"&VLOOKUP($W402,'1260-17A_SLOT_1'!$A$2:$F
> > $81,5,FALSE),IF(AND($D402="INPUT",$J402="")=TRUE,VLOOKUP($I402,$B$2:$M
> > $5000,12,FALSE),IF(AND(LEFT($L402,10)="DTS E1459A",
> > $J402="")=TRUE,VLOOKUP($I402,$B$2:$M$5000,12,FALSE),"")))
>
> > do you know how can I use your INDIRECT solution to refer to
> > '1260-17A_SLOT_1', where '1260-17A_SLOT_1' is contained in a cell
> > adjacent to the formula's cell, or at least on the same row ?- Hide quoted text -
>
> - Show quoted text -

Thanks JE. It works. This is a big help. This is my first
experience with embedding functions inside of other functions.

Jeff