taking data from different worksheets

[kman]

I have different worksheets on the file.
I woul'd like to have one worksheet that summarize data from th
different worksheets.
I woul'd like to built a formula that takes a varient as thw workshee
name.

for example:
my worksheets name are : p1 p2 p3 p4 p5 p6...

i would like a formula that takes the workshet name from cells tha
called1 p2 p3 ...

 

[zuapfly]

it would probably be solved similarly to an earlier problem of mine
take a look at
http://www.excelforum.com/showthread.php?s=&threadid=24005

 

[Frank Kabel]

Hi

try using INDIRECT
e.g.
if cell A1 contains the sheet name try
=INDIRECT("'" & A1 & " '!B1")

 

[kman]

when using

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

I want to copy it to one line down and that it will automaticall
changes the 1 to 2 meaning
=INDIRECT("'" & A2 & " '!B2")
but it only changes the first number and i get
=INDIRECT("'" & A2 & " '!B1")

How can I write the formule that it will change b1 to b2 too

 

[Frank Kabel]

Hi
try
=INDIRECT("'" & A1 & " '!B" & ROW(1:1))