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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Miner Jeff - 11 Jun 2007 22:57 GMT

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

Reply to this Message

JE McGimpsey - 11 Jun 2007 23:05 GMT

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

> Here's the dilemma:
>
[quoted text clipped - 17 lines]
>
> Jeff

Reply to this Message

Miner Jeff - 13 Jun 2007 15:23 GMT

> I'm not sure I understand what you mean, but if you've got worksheet
> names in column 2, then you can use
[quoted text clipped - 31 lines]
>
> - 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

Reply to this Message

JE McGimpsey - 13 Jun 2007 18:30 GMT

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

> Here's the real problem I'm trying to solve:
>
[quoted text clipped - 10 lines]
> '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 ?

Reply to this Message

Miner Jeff - 13 Jun 2007 20:56 GMT

> One way:
>
[quoted text clipped - 25 lines]
>
> - 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

Reply to this Message