How do I lookup data from multiple worksheets?

[Richard]

I am trying to make a single worksheet that references information from
multiple different worksheets. If I name the individual worksheets 1, 2, 3,
4... shouldn't I be able to have a worksheet that will display whatever is in
cell A1 of each worksheet without having to manually link the sheets. I
tried linking cell A1 of worksheet 1 to cell A1 of the main worksheet and
that worked fine. When I tried to copy the formula to the next column and
simply change the reference to worksheet 2, it doesn't work. Please help.

 

[PJFry]

Please post the formulas you are using.

 

[Luke M]

Try using the INDIRECT function:
=INDIRECT(COLUMN()&"!A1")

Note that copying this across will change address from 1!A1 to 2!A1, but
that copying down will not change it at all. To adjust the cell reference as
well, you could try playing around with the reference, just remember to use
the ampersand to concatenate the text and function parts of your formula.