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!

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!

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!

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!

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.

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
---

Modifying only the worksheet when draging down a cell reference
 

You could use Pete's INDIRECT formula if you had a list of the sheetnames in a
worksheet.

Run this macro to get the list of 68 sheetnames on a new sheet named List.

Private Sub ListSheets()
Dim rng As Range
Dim i As Integer
Worksheets.Add(after:=Worksheets(Worksheets.Count) ).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

Then on Table sheet alter Pete's formula to...................

=INDIRECT(List!A1&"!C$11")


Gord Dibben MS Excel MVP

On Thu, 27 Sep 2007 07:17:26 -0700, wrote:

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!

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

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

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

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

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

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
---

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

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

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

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

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

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

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*

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.