Adding contents of a cell as the worksheet reference in a formula

  • Thread starter Thread starter Miner Jeff
  • Start date Start date
M

Miner Jeff

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")
 
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")








- Show quoted text -
Click to expand...

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
 
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






- Show quoted text -
Click to expand...

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

Jeff
 
Back
Top