Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modifying only the worksheet when draging down a cell reference
How can I change only the worksheet when I drag down a cell
reference. these are all worksheets in an active workbook. We are creating a summary table in worksheet one from 68 worksheets in that workbook and the cells referenced are always the same but in sequential worksheets. Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modifying only the worksheet when draging down a cell reference
Assume the 68 worksheets are named as: Sheet1, Sheet2, ... Sheet68
and the target cell refs to be extracted from each sheet a X2, Z2 In your sheet named: Summary (say) Enter the target cell refs in B1 across, eg in B1: X2, in C1: Z2 Then place in B2: =INDIRECT("'Sheet"&ROWS($1:1)&"'!"&B$1) Copy B2 across to C2, fill down by 68 rows. Col B will return the contents from X2 inn each of the 68 sheets, while col C returns Z2 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ups.com... How can I change only the worksheet when I drag down a cell reference. these are all worksheets in an active workbook. We are creating a summary table in worksheet one from 68 worksheets in that workbook and the cells referenced are always the same but in sequential worksheets. Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modifying only the worksheet when draging down a cell reference
Are your worksheets numbered from Sheet2 to Sheet68? If you want to
get information from the same cell of the other worksheets, for example cell K13, then you could use something like this: =INDIRECT("Sheet"&ROW()&"!K$13") Put the first formula in row 2 of Sheet1, then copy down. Hope this helps. Pete On Sep 27, 2:09 pm, wrote: How can I change only the worksheet when I drag down a cell reference. these are all worksheets in an active workbook. We are creating a summary table in worksheet one from 68 worksheets in that workbook and the cells referenced are always the same but in sequential worksheets. Thank you! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modifying only the worksheet when draging down a cell reference
On Sep 27, 9:44 am, Pete_UK wrote:
Are your worksheets numbered from Sheet2 to Sheet68? If you want to get information from the same cell of the other worksheets, for example cell K13, then you could use something like this: =INDIRECT("Sheet"&ROW()&"!K$13") Put the first formula in row 2 of Sheet1, then copy down. Hope this helps. Pete On Sep 27, 2:09 pm, wrote: How can I change only the worksheet when I drag down a cell reference. these are all worksheets in an active workbook. We are creating a summary table in worksheet one from 68 worksheets in that workbook and the cells referenced are always the same but in sequential worksheets. Thank you!- Hide quoted text - - Show quoted text - The sheets all have names such as iye, iyz, iyh. The data is on sheet iyz (or iye, iyh), cell C11 and needs to go to sheet Table, cell A3, A4, A5...down. Thank you! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modifying only the worksheet when draging down a cell reference
In Table,
List the sheetnames: iye, iyz, iyh, ... in B2 down Then place in A3: =INDIRECT("'"&B2&"'!C11") Copy A3 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote The sheets all have names such as iye, iyz, iyh. The data is on sheet iyz (or iye, iyh), cell C11 and needs to go to sheet Table, cell A3, A4, A5...down. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Modifying only the worksheet when draging down a cell reference
Oops, it should have read:
List the sheetnames: iye, iyz, iyh, ... in B3 down Then place in A3: =INDIRECT("'"&B3&"'!C11") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
I am trying to follow your advice. I have unsuccessfully tried to
consolidate worksheets using Data Consolidate. I use Excel 2003. As an example--Let us say my data is in 3 columns (B,C,D) and 10 rows (2,3,4,5,6,7,8,9,10,11) in each of 6 worksheets in the same workbook. All of the worksheets are set up the same--have the same layout. The text headings are in A1, B1, C1, D1. A1 is OBJECT CLASS. B1 is FY 2007, C1 is FY 2008, D1 is FY 2009. In column A I have have text labels that happen to be numbers. The labels which are in cells A2 thru A11 a 2511, 2521, 2522, 2523, 2530, 2531, 2561, 2571, 2572, 2573. In cell A14 I have the label TOTAL. Cell B14 is the sum of the numbers in cell B2 thru B11. Cell C14 is the sum of the numbers in cells C2 thru C11. Cell D14 is the sum of the numbers in cells D2 thru D11. The text column names (A1 thru D1) and row names A2 thru A11) are the same in each spreadsheet. I want to summarize the data of the 6 worksheets into a seventh worksheet that is identical in layout to the 6 worksheets. Let's say the 6 worksheets are named: PIA, Support Services, Region 1, Region 2, Region 3, and DMC. The 7th worksheet is named SUMMARY. What formulas do I place in worksheet SUMMARY into cells (B2 thru B11 and B14) and cells (C2 thru C11 and C14) and cells (D2 thru D11 and D14)? Thank you in advance for any help you might provide. Steve G |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Find the original thread and post your question and description as a reply in
that thread. I don't find anything in google search that relates but google has not been that reliable for a while. I have no idea what my first response was and don't wish to start all over again. Gord Dibben MS Excel MVP On Thu, 27 Sep 2007 12:51:58 -0700, Steve G wrote: I am trying to follow your advice. I have unsuccessfully tried to consolidate worksheets using Data Consolidate. I use Excel 2003. As an example--Let us say my data is in 3 columns (B,C,D) and 10 rows (2,3,4,5,6,7,8,9,10,11) in each of 6 worksheets in the same workbook. All of the worksheets are set up the same--have the same layout. The text headings are in A1, B1, C1, D1. A1 is OBJECT CLASS. B1 is FY 2007, C1 is FY 2008, D1 is FY 2009. In column A I have have text labels that happen to be numbers. The labels which are in cells A2 thru A11 a 2511, 2521, 2522, 2523, 2530, 2531, 2561, 2571, 2572, 2573. In cell A14 I have the label TOTAL. Cell B14 is the sum of the numbers in cell B2 thru B11. Cell C14 is the sum of the numbers in cells C2 thru C11. Cell D14 is the sum of the numbers in cells D2 thru D11. The text column names (A1 thru D1) and row names A2 thru A11) are the same in each spreadsheet. I want to summarize the data of the 6 worksheets into a seventh worksheet that is identical in layout to the 6 worksheets. Let's say the 6 worksheets are named: PIA, Support Services, Region 1, Region 2, Region 3, and DMC. The 7th worksheet is named SUMMARY. What formulas do I place in worksheet SUMMARY into cells (B2 thru B11 and B14) and cells (C2 thru C11 and C14) and cells (D2 thru D11 and D14)? Thank you in advance for any help you might provide. Steve G |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Think yours is a much simpler case, Steve.
Try this quick set-up Insert 2 new blank sheets, name these as simply: Start, End Move the 6 identical structure source sheets, viz: PIA, Support Services, Region 1, Region 2,Region 3, DMC in-between Start and End ("sandwich" the 6 sources in-between) Then in your identically structured SUMMARY, (this sheet must be placed *outside* the sandwich above) Place in B2: =SUM(Start:End!B2) Copy B2 across and fill down to D11 to populate -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steve G" wrote in message oups.com... I am trying to follow your advice. I have unsuccessfully tried to consolidate worksheets using Data Consolidate. I use Excel 2003. As an example--Let us say my data is in 3 columns (B,C,D) and 10 rows (2,3,4,5,6,7,8,9,10,11) in each of 6 worksheets in the same workbook. All of the worksheets are set up the same--have the same layout. The text headings are in A1, B1, C1, D1. A1 is OBJECT CLASS. B1 is FY 2007, C1 is FY 2008, D1 is FY 2009. In column A I have have text labels that happen to be numbers. The labels which are in cells A2 thru A11 a 2511, 2521, 2522, 2523, 2530, 2531, 2561, 2571, 2572, 2573. In cell A14 I have the label TOTAL. Cell B14 is the sum of the numbers in cell B2 thru B11. Cell C14 is the sum of the numbers in cells C2 thru C11. Cell D14 is the sum of the numbers in cells D2 thru D11. The text column names (A1 thru D1) and row names A2 thru A11) are the same in each spreadsheet. I want to summarize the data of the 6 worksheets into a seventh worksheet that is identical in layout to the 6 worksheets. Let's say the 6 worksheets are named: PIA, Support Services, Region 1, Region 2, Region 3, and DMC. The 7th worksheet is named SUMMARY. What formulas do I place in worksheet SUMMARY into cells (B2 thru B11 and B14) and cells (C2 thru C11 and C14) and cells (D2 thru D11 and D14)? Thank you in advance for any help you might provide. Steve G |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Reading from the "new" subject line changed by Steve ..
think Steve was just latching onto the discussions here, Gord. There's no history. His earlier posting was his first. I've proposed a "sandwich" treatment to Steve <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Find the original thread and post your question and description as a reply in that thread. I don't find anything in google search that relates but google has not been that reliable for a while. I have no idea what my first response was and don't wish to start all over again. Gord Dibben MS Excel MVP |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
On Sep 27, 6:43 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Find the original thread and post your question and description as a reply in that thread. I don't find anything in google search that relates but google has not been that reliable for a while. I have no idea what my first response was and don't wish to start all over again. Gord Dibben MS Excel MVP Mr. Dibben-- This is the original thread. Your earlier remark are above. I just changed the subject by putting the names of the authors before the name of the subject. Steve G |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
"Steve G" wrote:
.. I just changed the subject by putting the names of the authors before the name of the subject. Steve, I've given you some thoughts on your query in my earlier response. btw, recollect reading that one shouldn't change an existing thread's subject line because it'll screw up google's archiving process, which might make it that much tougher for everyone to search for stuff in future. You could just put in your query as a fresh new posting (think this is the preferred approach). Or if you want to jump-in, just do so with some intro/clarification in your reply, but do not change the subject line. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
I see that Max.
Good fix. Gord On Fri, 28 Sep 2007 07:46:47 +0800, "Max" wrote: Reading from the "new" subject line changed by Steve .. think Steve was just latching onto the discussions here, Gord. There's no history. His earlier posting was his first. I've proposed a "sandwich" treatment to Steve <g |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Gord, you're not kidding about Google being unreliable - I keep
responding to threads which look unanswered, only to find when I've replied that there were two or three earlier answers which suddenly materialise !! Pete On Sep 27, 11:43 pm, Gord Dibben <gorddibbATshawDOTca wrote: Find the original thread and post your question and description as a reply in that thread. I don't find anything in google search that relates but google has not been that reliable for a while. I have no idea what my first response was and don't wish to start all over again. Gord Dibben MS Excel MVP |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Steve
I am trying to follow your advice. I have unsuccessfully tried to consolidate worksheets using Data Consolidate. I use Excel 2003. Perhaps this statement made me think I had replied to some earlier posting and given you some advice. Gord On Thu, 27 Sep 2007 16:51:33 -0700, Steve G wrote: On Sep 27, 6:43 pm, Gord Dibben <gorddibbATshawDOTca wrote: Find the original thread and post your question and description as a reply in that thread. I don't find anything in google search that relates but google has not been that reliable for a while. I have no idea what my first response was and don't wish to start all over again. Gord Dibben MS Excel MVP Mr. Dibben-- This is the original thread. Your earlier remark are above. I just changed the subject by putting the names of the authors before the name of the subject. Steve G |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Try this search string at http://groups.google.com/?&
dual monitor group:*Excel* Talk about screwed up<g Gord On Thu, 27 Sep 2007 17:24:26 -0700, Pete_UK wrote: Gord, you're not kidding about Google being unreliable - I keep responding to threads which look unanswered, only to find when I've replied that there were two or three earlier answers which suddenly materialise !! Pete On Sep 27, 11:43 pm, Gord Dibben <gorddibbATshawDOTca wrote: Find the original thread and post your question and description as a reply in that thread. I don't find anything in google search that relates but google has not been that reliable for a while. I have no idea what my first response was and don't wish to start all over again. Gord Dibben MS Excel MVP |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Dear Mr. Dibben, Pete_UK, and Max--
I am sorry for the confusion about the thread. Google gives one the opportunity to "Edit Subject" so I thought that was okay. I will not do that again. Max's solution worked perfectly. Thank you. Now summing data to a summary spreadsheet from spreadsheets layed out identically is very easy. I tried Gordon Dibben's suggestion to: Try this search string at http://groups.google.com/?& dual monitor group:*Excel* I am not sure what you are trying to say. Thanks again for the support. You made my job with Uncle Sam easier. Steve G |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
Surely, Uncle Sam should be training you adequately !!
I think Gord's reply was to me - Google has been playing up recently. Glad you got something out of the thread (looks a bit of a mess now <bg) Pete On Sep 28, 3:01 pm, Steve G wrote: Dear Mr. Dibben, Pete_UK, and Max-- I am sorry for the confusion about the thread. Google gives one the opportunity to "Edit Subject" so I thought that was okay. I will not do that again. Max's solution worked perfectly. Thank you. Now summing data to a summary spreadsheet from spreadsheets layed out identically is very easy. I tried Gordon Dibben's suggestion to: Try this search string at http://groups.google.com/?& dual monitor group:*Excel* I am not sure what you are trying to say. Thanks again for the support. You made my job with Uncle Sam easier. Steve G |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
That was a reply to Pete_UK's posting google search reliability I had
mentioned in my first response to you. Gord On Fri, 28 Sep 2007 07:01:37 -0700, Steve G wrote: I tried Gordon Dibben's suggestion to: Try this search string at http://groups.google.com/?& dual monitor group:*Excel* |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To: Gordon Dibben, Pete_UK and Max Modifying only the worksheet when draging down a cell reference
welcome, Steve.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steve G" wrote: Max's solution worked perfectly. Thank you. Now summing data to a summary spreadsheet from spreadsheets layed out identically is very easy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Draging VLOOKUP to last cell. | New Users to Excel | |||
How to reference cell in other worksheet | Excel Worksheet Functions | |||
Problem with draging a formula,one cell value fixed,trivial question | Excel Discussion (Misc queries) | |||
Reference another worksheet using a cell | Excel Discussion (Misc queries) | |||
Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions |