Formula for Next Sheet and Every Other Cell

[JEverhart]

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

 

[Pete_UK]

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

Pete

 

[JEverhart]

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?

 

[Pete_UK]

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

 

[Pete_UK]

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

 

[Gord Dibben]

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

 

[Pete_UK]

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

Pete

 

[Gord Dibben]

Every little bit helps Pete.

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


Gord

 

[misteremanca]

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

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

 

[misteremanca]

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]

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

 

[misteremanca]

Thanks very much Gord! I've got it.

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

Cheers

eman