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