use Cell text as sheet reference

[Abdul]

Hello,

I have several worksheets in my workbook(1)

and in workbook (2) all the sheet names of workbook(1) in Cells A1 thru
X1

can i use the text in cell A1 thru X1 in a formula to reference the
workbook(1)

for example in cell A2 instead of ='[Workbook 1.xls]Sheet1l'!$A$2 can
i reference Cell A1 in workbook (2) instead of sheet1, since in
workbook (2) cell A1 contains text "sheet1 "

I am looking for a straight formula rather than VBA

 

[Gary''s Student]

You can construct references completely from cell text. For example in Book2:

in A1 put Book1
in A2 put accounts
in A3 put B9

The formula:
=INDIRECT("[" & A1 & ".xls]" & A2 & "!" & A3)
will give the same result as the forrmula:
=[Book1.xls]accounts!$B$9

 

[Jim May]

Give this a try:
In your Wb2:

=INDIRECT("'[YourWb1.xls]"& $A$1&"'!A1")
=INDIRECT("'[YourWb1.xls]"& $B$1&"'!A1")
=INDIRECT("'[YourWb1.xls]"& $C$1&"'!A1")

YourWb1 MUST BE OPEN!! << In order for it to work.

Jim May

 

[Abdul]

thanks for the reply.

I find it working but as an absolute reference. When I want to copy the
formula to the next cell instead of A2 it gives A1 since "!A1" is fixed
as a rext. How I can get it to work when I copy the formula to the next
cell so the formula should look like

=INDIRECT("'[YourWb1.xls]"& $C$1&"'!A2")
if i put A1 out of quotes then it refers to the active sheet rather
than the other workbook cell

thanks

Give this a try:
In your Wb2:

=INDIRECT("'[YourWb1.xls]"& $A$1&"'!A1")
=INDIRECT("'[YourWb1.xls]"& $B$1&"'!A1")
=INDIRECT("'[YourWb1.xls]"& $C$1&"'!A1")

YourWb1 MUST BE OPEN!! << In order for it to work.

Jim May

Hello,

I have several worksheets in my workbook(1)

and in workbook (2) all the sheet names of workbook(1) in Cells A1 thru
X1

can i use the text in cell A1 thru X1 in a formula to reference the
workbook(1)

for example in cell A2 instead of ='[Workbook 1.xls]Sheet1l'!$A$2 can
i reference Cell A1 in workbook (2) instead of sheet1, since in
workbook (2) cell A1 contains text "sheet1 "

I am looking for a straight formula rather than VBA