Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Formula for Next Sheet and Every Other Cell

I have a workbook with multiple sheets. I would like to link each sheet to a
Summary sheet. The tricky part is that I would like to enter the formula one
time and then copy as there are about 10 columns across and 200 rows down.
The formula I have is:
='Sheet Name'!Cell Reference

I need the sheet name in the formula to increment to the next sheet in the
workbook when copied down the 200 rows with the cell reference remaining
constant
For Example:

Row 1 ='Sheet Name+1'!Cell Reference
Row 2 ='Sheet Name+2'!Cell Reference

I also need the cell reference to increment by 2 (every other) and the sheet
name to remain constant when copied across the 10 rows.
For Example:

Column A Column B
='Sheet Name'!Cell Reference ='Sheet Name'!Cell Reference +2
--
JEverhart
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Formula for Next Sheet and Every Other Cell

Okay, so what's the first cell reference you will have?

Pete

On May 16, 8:44*pm, JEverhart
wrote:
I have a workbook with multiple sheets. *I would like to link each sheet to a
Summary sheet. *The tricky part is that I would like to enter the formula one
time and then copy as there are about 10 columns across and 200 rows down. *
The formula I have is:
* * *='Sheet Name'!Cell Reference

I need the sheet name in the formula to increment to the next sheet in the
workbook when copied down the 200 rows with the cell reference remaining
constant
* * *For Example:

Row 1 * * * * *='Sheet Name+1'!Cell Reference
Row 2 * * * * *='Sheet Name+2'!Cell Reference

I also need the cell reference to increment by 2 (every other) and the sheet
name to remain constant when copied across the 10 rows.
* * *For Example:

* * * * * * *Column A * * * * * * * * * * * * * * * * *Column B
* * * * * ='Sheet Name'!Cell Reference * * * ='Sheet Name'!Cell Reference +2 *
--
JEverhart


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Formula for Next Sheet and Every Other Cell

Ah! I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:

A. Vision Mission Values

Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:

=INDIRECT("'Sheet"&ROW(A1)&"'!D48")

This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.

If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:

=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")

Then you could copy this across 9 columns and then copy these 10
columns down.

However, you would need to change your sheet names for this to work.

Pete


On May 16, 10:30*pm, JEverhart
wrote:
The first sheet name is 'A. *Vision Mission Values'
The first cell reference is D48 on the above named sheet.

So my first formula is ='A. *Vision MIssion Values'!D48.

What can I do to this formula to cause the 'Sheet Name' to progress to the
next sheet in the same workbook and and also cause the cell reference to skip
E48 and go to F48?

--
JEverhart



"Pete_UK" wrote:
Okay, so what's the first cell reference you will have?


Pete


On May 16, 8:44 pm, JEverhart
wrote:
I have a workbook with multiple sheets. *I would like to link each sheet to a
Summary sheet. *The tricky part is that I would like to enter the formula one
time and then copy as there are about 10 columns across and 200 rows down. *
The formula I have is:
* * *='Sheet Name'!Cell Reference


I need the sheet name in the formula to increment to the next sheet in the
workbook when copied down the 200 rows with the cell reference remaining
constant
* * *For Example:


Row 1 * * * * *='Sheet Name+1'!Cell Reference
Row 2 * * * * *='Sheet Name+2'!Cell Reference


I also need the cell reference to increment by 2 (every other) and the sheet
name to remain constant when copied across the 10 rows.
* * *For Example:


* * * * * * *Column A * * * * * * * * * * * * * * * * *Column B
* * * * * ='Sheet Name'!Cell Reference * * * ='Sheet Name'!Cell Reference +2 *
--
JEverhart- Hide quoted text -


- Show quoted text -


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Formula for Next Sheet and Every Other Cell

Thinking further on this, you could avoid having to rename all your
sheets if you were to list them somewhere in your summary sheet (for
example in cells X1:X200). Then you could use this formula in your
first cell:

=INDIRECT("'"&INDEX($X$1:$X
$200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48")

Note the apostrophes - between the first set of quotes and immediately
before the exclamation mark. These will allow the use of spaces in
your names. Your sheet names in X1:X200 must be typed exactly as they
appear on the tabs, including any leading or trailing spaces.

Now you can copy this across and down as required.

Hope this helps.

Pete

On May 17, 2:50*am, Pete_UK wrote:
Ah! *I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:

A. *Vision Mission Values

Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:

=INDIRECT("'Sheet"&ROW(A1)&"'!D48")

This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.

If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:

=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")

Then you could copy this across 9 columns and then copy these 10
columns down.

However, you would need to change your sheet names for this to work.

Pete

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Formula for Next Sheet and Every Other Cell

If you go with Pete's suggestion, here is macro to get the list of sheet names
into column X

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 24).Value = ws.Name
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Sat, 17 May 2008 05:20:50 -0700 (PDT), Pete_UK wrote:

Thinking further on this, you could avoid having to rename all your
sheets if you were to list them somewhere in your summary sheet (for
example in cells X1:X200). Then you could use this formula in your
first cell:

=INDIRECT("'"&INDEX($X$1:$X
$200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48")

Note the apostrophes - between the first set of quotes and immediately
before the exclamation mark. These will allow the use of spaces in
your names. Your sheet names in X1:X200 must be typed exactly as they
appear on the tabs, including any leading or trailing spaces.

Now you can copy this across and down as required.

Hope this helps.

Pete

On May 17, 2:50*am, Pete_UK wrote:
Ah! *I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:

A. *Vision Mission Values

Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:

=INDIRECT("'Sheet"&ROW(A1)&"'!D48")

This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.

If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:

=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")

Then you could copy this across 9 columns and then copy these 10
columns down.

However, you would need to change your sheet names for this to work.

Pete




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Formula for Next Sheet and Every Other Cell

Thanks for that, Gord - quicker than typing 200 names !! <bg

Pete

On May 17, 5:30*pm, Gord Dibben <gorddibbATshawDOTca wrote:
If you go with Pete's suggestion, here is macro to get the list of sheet names
into column X

Sub CreateListOfSheetsOnFirstSheet()
* * Dim ws As Worksheet
* * For i = 1 To Worksheets.Count
* * * * With Worksheets(1)
* * * * * * Set ws = Worksheets(i)
* * * * * * .Cells(i, 24).Value = ws.Name
* * * * End With
* * Next i
End Sub

Gord Dibben *MS Excel MVP



On Sat, 17 May 2008 05:20:50 -0700 (PDT), Pete_UK wrote:
Thinking further on this, you could avoid having to rename all your
sheets if you were to list them somewhere in your summary sheet (for
example in cells X1:X200). Then you could use this formula in your
first cell:


=INDIRECT("'"&INDEX($X$1:$X
$200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48")


Note the apostrophes - between the first set of quotes and immediately
before the exclamation mark. These will allow the use of spaces in
your names. Your sheet names in X1:X200 must be typed exactly as they
appear on the tabs, including any leading or trailing spaces.


Now you can copy this across and down as required.


Hope this helps.


Pete


On May 17, 2:50*am, Pete_UK wrote:
Ah! *I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:


A. *Vision Mission Values


Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:


=INDIRECT("'Sheet"&ROW(A1)&"'!D48")


This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.


If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:


=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")


Then you could copy this across 9 columns and then copy these 10
columns down.


However, you would need to change your sheet names for this to work.


Pete- Hide quoted text -


- Show quoted text -


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Formula for Next Sheet and Every Other Cell

Every little bit helps Pete.

I'm lazy so found the macro excellent for this type of operation when sheetnames
are not standard.


Gord

On Sat, 17 May 2008 10:58:01 -0700 (PDT), Pete_UK wrote:

Thanks for that, Gord - quicker than typing 200 names !! <bg

Pete

On May 17, 5:30*pm, Gord Dibben <gorddibbATshawDOTca wrote:
If you go with Pete's suggestion, here is macro to get the list of sheet names
into column X

Sub CreateListOfSheetsOnFirstSheet()
* * Dim ws As Worksheet
* * For i = 1 To Worksheets.Count
* * * * With Worksheets(1)
* * * * * * Set ws = Worksheets(i)
* * * * * * .Cells(i, 24).Value = ws.Name
* * * * End With
* * Next i
End Sub

Gord Dibben *MS Excel MVP



On Sat, 17 May 2008 05:20:50 -0700 (PDT), Pete_UK wrote:
Thinking further on this, you could avoid having to rename all your
sheets if you were to list them somewhere in your summary sheet (for
example in cells X1:X200). Then you could use this formula in your
first cell:


=INDIRECT("'"&INDEX($X$1:$X
$200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48")


Note the apostrophes - between the first set of quotes and immediately
before the exclamation mark. These will allow the use of spaces in
your names. Your sheet names in X1:X200 must be typed exactly as they
appear on the tabs, including any leading or trailing spaces.


Now you can copy this across and down as required.


Hope this helps.


Pete


On May 17, 2:50*am, Pete_UK wrote:
Ah! *I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:


A. *Vision Mission Values


Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:


=INDIRECT("'Sheet"&ROW(A1)&"'!D48")


This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.


If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:


=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")


Then you could copy this across 9 columns and then copy these 10
columns down.


However, you would need to change your sheet names for this to work.


Pete- Hide quoted text -


- Show quoted text -


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Formula for Next Sheet and Every Other Cell

WOW....I can't believe I found 'almost' the help I needed exactly!

PETE (or anyone else who knows this one), how would I do this if I have the
same problem AND my sheet names are numbers.

ex. The formula I want to copy is: '1'!H3

When I copy it, I would like the sheet number to increment and the cell
number to remain fixed. The pasted result should be (in my perfect world):
'2'!H3

I've searched and searched, and had no luck. I'm grateful for any help that
may be out there...

Cheers

Sandy

"Pete_UK" wrote:

Ah! I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:

A. Vision Mission Values

Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:

=INDIRECT("'Sheet"&ROW(A1)&"'!D48")

This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.

If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:

=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")

Then you could copy this across 9 columns and then copy these 10
columns down.

However, you would need to change your sheet names for this to work.

Pete


On May 16, 10:30 pm, JEverhart
wrote:
The first sheet name is 'A. Vision Mission Values'
The first cell reference is D48 on the above named sheet.

So my first formula is ='A. Vision MIssion Values'!D48.

What can I do to this formula to cause the 'Sheet Name' to progress to the
next sheet in the same workbook and and also cause the cell reference to skip
E48 and go to F48?

--
JEverhart



"Pete_UK" wrote:
Okay, so what's the first cell reference you will have?


Pete


On May 16, 8:44 pm, JEverhart
wrote:
I have a workbook with multiple sheets. I would like to link each sheet to a
Summary sheet. The tricky part is that I would like to enter the formula one
time and then copy as there are about 10 columns across and 200 rows down.
The formula I have is:
='Sheet Name'!Cell Reference


I need the sheet name in the formula to increment to the next sheet in the
workbook when copied down the 200 rows with the cell reference remaining
constant
For Example:


Row 1 ='Sheet Name+1'!Cell Reference
Row 2 ='Sheet Name+2'!Cell Reference


I also need the cell reference to increment by 2 (every other) and the sheet
name to remain constant when copied across the 10 rows.
For Example:


Column A Column B
='Sheet Name'!Cell Reference ='Sheet Name'!Cell Reference +2
--
JEverhart- Hide quoted text -


- Show quoted text -



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Formula for Next Sheet and Every Other Cell

=INDIRECT(ROW(A1)&"!H3")


Gord Dibben MS Excel MVP


On Thu, 17 Jul 2008 12:53:15 -0700, misteremanca
wrote:

WOW....I can't believe I found 'almost' the help I needed exactly!

PETE (or anyone else who knows this one), how would I do this if I have the
same problem AND my sheet names are numbers.

ex. The formula I want to copy is: '1'!H3

When I copy it, I would like the sheet number to increment and the cell
number to remain fixed. The pasted result should be (in my perfect world):
'2'!H3

I've searched and searched, and had no luck. I'm grateful for any help that
may be out there...

Cheers

Sandy

"Pete_UK" wrote:

Ah! I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:

A. Vision Mission Values

Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:

=INDIRECT("'Sheet"&ROW(A1)&"'!D48")

This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.

If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:

=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")

Then you could copy this across 9 columns and then copy these 10
columns down.

However, you would need to change your sheet names for this to work.

Pete


On May 16, 10:30 pm, JEverhart
wrote:
The first sheet name is 'A. Vision Mission Values'
The first cell reference is D48 on the above named sheet.

So my first formula is ='A. Vision MIssion Values'!D48.

What can I do to this formula to cause the 'Sheet Name' to progress to the
next sheet in the same workbook and and also cause the cell reference to skip
E48 and go to F48?

--
JEverhart



"Pete_UK" wrote:
Okay, so what's the first cell reference you will have?

Pete

On May 16, 8:44 pm, JEverhart
wrote:
I have a workbook with multiple sheets. I would like to link each sheet to a
Summary sheet. The tricky part is that I would like to enter the formula one
time and then copy as there are about 10 columns across and 200 rows down.
The formula I have is:
='Sheet Name'!Cell Reference

I need the sheet name in the formula to increment to the next sheet in the
workbook when copied down the 200 rows with the cell reference remaining
constant
For Example:

Row 1 ='Sheet Name+1'!Cell Reference
Row 2 ='Sheet Name+2'!Cell Reference

I also need the cell reference to increment by 2 (every other) and the sheet
name to remain constant when copied across the 10 rows.
For Example:

Column A Column B
='Sheet Name'!Cell Reference ='Sheet Name'!Cell Reference +2
--
JEverhart- Hide quoted text -

- Show quoted text -




  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Formula for Next Sheet and Every Other Cell

Thanks VERY MUCH for the reply Gord. It worked like a charm.

I've done some searching since, on the INDIRECT function, and I can't seem
to figure out what the A refers to.

Could I have used ANY letter there?

Is there a site you could point me to that explains INDIRECT well?

Cheers

Alexander





"Gord Dibben" wrote:

=INDIRECT(ROW(A1)&"!H3")


Gord Dibben MS Excel MVP




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Formula for Next Sheet and Every Other Cell

ROW(A1) means the row number of A1 which is 1.........the name of your
worksheet 1

giving you the same as typing in =1!$H$3

As you drag the formula down the A1 will change to A2 but the H3 will not change
because it is enclosed in quotes

So in row 2 you will get =INDIRECT(ROW(A2)&"!H3"

giving you same as typing in =2!$H$3

The A is just a column letter

Yes.......you could change the column to any column letter.

i.e. =INDIRECT(ROW(Q1)&"!H3"

You could also use =INDIRECT(ROW(1:1)&"!H3" and drag that down.

See Debra Dalgleish's site for more on INDIRECT

http://www.contextures.on.ca/xlFunctions05.html


Gord

On Thu, 17 Jul 2008 15:24:08 -0700, misteremanca
wrote:

Thanks VERY MUCH for the reply Gord. It worked like a charm.

I've done some searching since, on the INDIRECT function, and I can't seem
to figure out what the A refers to.

Could I have used ANY letter there?

Is there a site you could point me to that explains INDIRECT well?

Cheers

Alexander





"Gord Dibben" wrote:

=INDIRECT(ROW(A1)&"!H3")


Gord Dibben MS Excel MVP



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Formula for Next Sheet and Every Other Cell

Thanks very much Gord! I've got it.

The word 'row' threw me off.....but I'm understanding it now.

Cheers

eman

"Gord Dibben" wrote:

ROW(A1) means the row number of A1 which is 1.........the name of your
worksheet 1

giving you the same as typing in =1!$H$3

As you drag the formula down the A1 will change to A2 but the H3 will not change
because it is enclosed in quotes

So in row 2 you will get =INDIRECT(ROW(A2)&"!H3"

giving you same as typing in =2!$H$3

The A is just a column letter

Yes.......you could change the column to any column letter.

i.e. =INDIRECT(ROW(Q1)&"!H3"

You could also use =INDIRECT(ROW(1:1)&"!H3" and drag that down.

See Debra Dalgleish's site for more on INDIRECT

http://www.contextures.on.ca/xlFunctions05.html


Gord

On Thu, 17 Jul 2008 15:24:08 -0700, misteremanca
wrote:

Thanks VERY MUCH for the reply Gord. It worked like a charm.

I've done some searching since, on the INDIRECT function, and I can't seem
to figure out what the A refers to.

Could I have used ANY letter there?

Is there a site you could point me to that explains INDIRECT well?

Cheers

Alexander


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
Can I annotate a formula result in Sheet 1 in Sheet 2 by Cell Addr Rebecca Bauer Excel Worksheet Functions 2 August 27th 07 07:20 PM
FORMULA COPY SHEET IN THE OTHER CELL Carol Excel Worksheet Functions 2 July 26th 06 05:03 PM
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 parag Excel Worksheet Functions 3 June 15th 06 10:29 PM
Lookup cell contents in on sheet based on a formula in second sheet Michael Wright via OfficeKB.com Excel Worksheet Functions 1 April 30th 05 04:11 PM
Is there a formula that will place the sheet name in a cell? Reed Excel Worksheet Functions 7 January 20th 05 02:29 AM


All times are GMT +1. The time now is 10:45 AM.

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"