Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP - Link Data from Different Sheets to Summary Page
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP - Link Data from Different Sheets to Summary Page
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 wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP - Link Data from Different Sheets to Summary Page
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" wrote: 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 wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP - Link Data from Different Sheets to Summary Page
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" soft.com wrote in message ... 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" wrote: 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 wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to create a way to link summary page to subsequent workshee | Excel Worksheet Functions | |||
Summary Sheets do not Link | Excel Discussion (Misc queries) | |||
Conditional Linking and Formatting across worksheets to form Summary Page - Only pull/link non-null fields | Excel Worksheet Functions | |||
selecting different cell ranges across sheets, to display on summary page | Excel Discussion (Misc queries) | |||
Summary of data from 20 sheets | Excel Discussion (Misc queries) |