Modifying only the worksheet when draging down a cell reference

[claudine_d]

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!

 

[Max]

Assume the 68 worksheets are named as: Sheet1, Sheet2, ... Sheet68
and the target cell refs to be extracted from each sheet are: 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

 

[Pete_UK]

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

 

[claudine_d]

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



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

 

[Max]

In Table,
List the sheetnames: iye, iyz, iyh, ... in B2 down
Then place in A3: =INDIRECT("'"&B2&"'!C11")
Copy A3 down

 

[Max]

Oops, it should have read:

List the sheetnames: iye, iyz, iyh, ... in B3 down
Then place in A3: =INDIRECT("'"&B3&"'!C11")


---

 

[Gord Dibben]

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

 

[Steve G]

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

 

[Gord Dibben]

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

 

[Max]

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]

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>

 

[Steve G]

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

 

[Max]

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

---

 

[Gord Dibben]

I see that Max.

Good fix.


Gord

 

[Pete_UK]

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

 

[Gord Dibben]

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

 

[Gord Dibben]

Try this search string at http://groups.google.com/?&

dual monitor group:*Excel*

Talk about screwed up<g>


Gord

 

[Steve G]

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

 

[Pete_UK]

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

 

[Gord Dibben]

That was a reply to Pete_UK's posting re: google search reliability I had
mentioned in my first response to you.


Gord