help me! about quote other workbook's sheets

  • Thread starter Thread starter lingling
  • Start date Start date
L

lingling

I have 2 workbooks. one is Book1, the other one is Book2.

There are about 100 sheets (sheet1 to sheet100) in Book2. And now,I want to
quote Book2's sheets in Book1. for example, I can use it this way :

[Book2.xls]sheetX!A3

the part "sheetX" would be right if I write it sheet1 or sheet2. But, my
problem is that I need the X could be changed via some cell's value. how can
I do it?

thanks!

(e-mail address removed)
 
Hi lingling,

=[Book2.xls]sheetX!A3

You would need to use INDIRECT and both workbooks must be
open.

=INDIRECT("[Book2.xls]" & H$1 & "!A3")
 
Hi David,

Thank you very much for your help!
I have learnt about INDIRECT and can use it in a simple way now, It works
for me! thank you so much!

But now, I have a new problem. I want to use it like this:
=vlookup(A1,[Book2.xls]sheetX!$a$1:$c$100,3,false)

How can I use INDIRECT in it? It says wrong when I wrote it like this:
=vlookup(A1,indirect("[Book2.xls]" & $H$1 & "!")$a$1:$c$100,3,false))
or =vlookup(A1,[Book2.xls]indirect(("$H$1") & "!")$a$1:$c$100,3,false)

whats wrong with me? I am lost...please help me! thank you !

lingling
(e-mail address removed)

P.S. I have checked your webiste.Its amazing! There are so many things about
excel.But I could not figure out how to find my answer.


David McRitchie said:
Hi lingling,

=[Book2.xls]sheetX!A3

You would need to use INDIRECT and both workbooks must be
open.

=INDIRECT("[Book2.xls]" & H$1 & "!A3")

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

lingling said:
I have 2 workbooks. one is Book1, the other one is Book2.

There are about 100 sheets (sheet1 to sheet100) in Book2. And now,I want to
quote Book2's sheets in Book1. for example, I can use it this way :

[Book2.xls]sheetX!A3

the part "sheetX" would be right if I write it sheet1 or sheet2. But, my
problem is that I need the X could be changed via some cell's value. how can
I do it?

thanks!

(e-mail address removed)
Click to expand...
Click to expand...
 
Try including that whole string inside =indirect()

=VLOOKUP(A1,INDIRECT("[Book2.xls]"&$H$1&"!$a$1:$c$100"),3,FALSE)


Hi David,

Thank you very much for your help!
I have learnt about INDIRECT and can use it in a simple way now, It works
for me! thank you so much!

But now, I have a new problem. I want to use it like this:
=vlookup(A1,[Book2.xls]sheetX!$a$1:$c$100,3,false)

How can I use INDIRECT in it? It says wrong when I wrote it like this:
=vlookup(A1,indirect("[Book2.xls]" & $H$1 & "!")$a$1:$c$100,3,false))
or =vlookup(A1,[Book2.xls]indirect(("$H$1") & "!")$a$1:$c$100,3,false)

whats wrong with me? I am lost...please help me! thank you !

lingling
(e-mail address removed)

P.S. I have checked your webiste.Its amazing! There are so many things about
excel.But I could not figure out how to find my answer.

David McRitchie said:
Hi lingling,

=[Book2.xls]sheetX!A3

You would need to use INDIRECT and both workbooks must be
open.

=INDIRECT("[Book2.xls]" & H$1 & "!A3")

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

lingling said:
I have 2 workbooks. one is Book1, the other one is Book2.

There are about 100 sheets (sheet1 to sheet100) in Book2. And now,I want to
quote Book2's sheets in Book1. for example, I can use it this way :

[Book2.xls]sheetX!A3

the part "sheetX" would be right if I write it sheet1 or sheet2. But, my
problem is that I need the X could be changed via some cell's value. how can
I do it?

thanks!

(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much ! You are right! It works!
I think I tried this too. not sure why It did not work. perhaps I typed
wrong..:-P

lingling
(e-mail address removed)

Dave Peterson said:
Try including that whole string inside =indirect()

=VLOOKUP(A1,INDIRECT("[Book2.xls]"&$H$1&"!$a$1:$c$100"),3,FALSE)


Hi David,

Thank you very much for your help!
I have learnt about INDIRECT and can use it in a simple way now, It works
for me! thank you so much!

But now, I have a new problem. I want to use it like this:
=vlookup(A1,[Book2.xls]sheetX!$a$1:$c$100,3,false)

How can I use INDIRECT in it? It says wrong when I wrote it like this:
=vlookup(A1,indirect("[Book2.xls]" & $H$1 & "!")$a$1:$c$100,3,false))
or =vlookup(A1,[Book2.xls]indirect(("$H$1") & "!")$a$1:$c$100,3,false)

whats wrong with me? I am lost...please help me! thank you !

lingling
(e-mail address removed)

P.S. I have checked your webiste.Its amazing! There are so many things about
excel.But I could not figure out how to find my answer.

David McRitchie said:
Hi lingling,

=[Book2.xls]sheetX!A3

You would need to use INDIRECT and both workbooks must be
open.

=INDIRECT("[Book2.xls]" & H$1 & "!A3")

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I have 2 workbooks. one is Book1, the other one is Book2.

There are about 100 sheets (sheet1 to sheet100) in Book2. And now,I
Click to expand...
want
to
quote Book2's sheets in Book1. for example, I can use it this way :

[Book2.xls]sheetX!A3

the part "sheetX" would be right if I write it sheet1 or sheet2. But, my
problem is that I need the X could be changed via some cell's value.
Click to expand...
how
can
I do it?

thanks!

(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top