Referencing Between Different Sheets

Hi,

I am in the process of creating a workbook and need a little help.

I have set up worksheets within my document - each containing information relating to a seperate construction project.

I then have another worksheet that I want to copy the information across so that it creates a table of the information so that I can then create comparison graphs etc.

I am using =Sheet1!A1 to link the information from my project sheet to my combined table. I have about 60+ or so per sheet so manually recreating this for each is not possible.

Is there a formula or something that when I create another worksheet (for example 2) that within the combined table it will then complete the infromation found within the specified cells?

Many Thanks in advance!!

Suppose you had the names of the sheets in B1, C1, D1 etc. Then you could have this formula in B2:

=INDIRECT("'"&B$1&"'!A1")

to bring the data from cell A1 of the sheet listed in B1. The apostrophes are there to allow for spaces in the sheet names. If you then copy this across you will get the data from cell A1 of the sheet listed in C1, and so on.

Is this what you mean?

Hope this helps.

Pete

Pete,

It does sound like this is what I am after although I am still having a little trouble getting it to return a value.

Haven't actually used Indirect function in the past.

The cell information I am trying to copy across is on a sheet called Template and is in cell Q23

I have the name of the worksheet typed in cell A5 on my combined sheet.

Could you please type the formula as I should so that i can make sure Im not making a silly mistake?

thanks

The formula would look like this:

=INDIRECT("'"&A5&"'!Q23")

The idea is that you would then have a list of other sheet names in A6 downwards, and then you can just copy that formula down so as to bring data from cell Q23 of each cell in turn.

You can upload a copy of your file to this site if you still have problems - basically, click on Go Advanced while posting a message, and then you can click on the paperclip icon or scroll below your text and use Manage Attachments and follow the steps (the FAQ has more details).

Hope this helps.

Pete