Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
---


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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!


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 44
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 44
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 44
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Draging VLOOKUP to last cell. mohd21uk via OfficeKB.com New Users to Excel 4 May 15th 06 04:27 PM
How to reference cell in other worksheet JimDandy Excel Worksheet Functions 4 December 17th 05 07:41 PM
Problem with draging a formula,one cell value fixed,trivial question marko Excel Discussion (Misc queries) 3 November 14th 05 12:22 AM
Reference another worksheet using a cell Lynxbci3 Excel Discussion (Misc queries) 1 November 2nd 05 02:08 PM
Worksheet reference (i.e placing worksheet name in a cell) Roger Roger Excel Worksheet Functions 1 January 20th 05 04:40 PM


All times are GMT +1. The time now is 07:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"