PLEASE HELP - Link Data from Different Sheets to Summary Page

[Link Sheets to Summary Page - NEED HELP]

I have a summary page and I want to link data. The data comes from different
sheets in the workbook but the same cell on the sheet... is there a shortcut
or fast way to do this? I need to link 110 pages to one summary sheet.

 

[Bernard Liengme]

If you want to add all the cells use something like =SUM(Sheet1:Sheet2!A1)

This formula in ANY cell of ANY sheet will display what is in Z1 of Sheet1:
=INDIRECT("Sheet"&ROW(A1)&"!Z1")
You can copy it down the column to display what is in A1 of Sheet2, A1 of
Sheet3, etc

If something else is meant by 'link' please give us more details.

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Link Sheets to Summary Page - NEED HELP" <Link Sheets to Summary Page -
NEED (e-mail address removed)> wrote in message
news:[email protected]...

 

[Link Sheets to Summary Page - NEED HELP]

Ok, that didn't work. I'll try to be more specific. I have a sum value at
the bottom of each sheet in a large workbook that totals all the hours on
that sheet. I then have a summary page where I need those total hours for
each sheet to be represented indiviually into a cell. At the bottom of the
cover sheet I need a total of all the hours. For ex. On sheet EWO 0001 N46
represents 10 hours on sheet EWO 0002 N46 represents 25 hours... so on and so
further for a 110 more sheets.

On my summary page line A2:J2 represents EWO 0001 I need cell G2 to link to
sheet EWO 0001 N46, A3:J3 represents EWO 0002 I need cell G3 to link to sheet
EWO 0002 N46 so on and so further thru 110 more...

Does that make sense? I know that I can have G2 on the summary sheet ='EWO
0001'!N46 and continue to do this all the down but I was hoping there was a
faster way then putting = in the cell then clicking sheet EWO 0001 and
clicking N46 and then hitting enter and going to the next one and repeating
the steps.

 

[Bernard Liengme]

We are aiming to make a formula that results in =Your Sheet Name!N46
Which is what =INDIRECT("Sheet"&ROW(A1)&"!Z1" in my example
BUT ---- your sheet names have spaces in them so we must enclose them in
single quotes

I have three sheets that are named as Ew 01, Ew 02, and Ew 03
In D1 of each I have the numbers 5, 6, and 7, respectively

On my summary sheet (in Cell B14 but it could be anywhere) I have the
formula
=INDIRECT("'Ew 0"&ROW(A1)&"'!D1")
That is
=INDIRECT( double-quote singe-quote Ew 0 double-quote &ROW(A1) double-quote
single- quote !D1 double-quote.
This will evaluate to =INDIRECT("'Ew 01'!D1) and point to 'Ew 01'!D1
The cell displays the 5 from that cell

I copy this down one row and it becomes
=INDIRECT("'Ew 0"&ROW(A2)&"'!D1")
This will evaluate to =INDIRECT("'Ew 02'!D1) and point to 'Ew 02'!D1
The cell displays the 6 from that cell

And so on
It does work but you must get the quotes correct
Email me directly (remove TRUENORTH.) if you want a sample worksheet
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Link Sheets to Summary Page - NEED HELP"