Sheet references

  • Thread starter pablo bellissimo
  • Start date
P

pablo bellissimo

Hi All is there a way for me to drag down a formula that will change the
sheet it is looking at in each new cell?

lets say that in cell A1 the formula is =MySheet2!A1

As I replicate this down I want the sheet2 to increase to MySheet3, then
MySheet4 etc. The sheets might not be called 2,3,4,5 etc but I need the
formula to just go to the next sheet regardless of the name of it.

I really hope this makes sense!

Thanks again guys
 
G

Gary''s Student

Very easy:

In an unused column, say column B, enter your sheet names exclaimed:

Sales!
Revenue!

The in A1 enter:
=INDIRECT(B1 & "A1")
and copy down
 
P

pablo bellissimo

Thanks for the reply.

Is there a way for Excel to find the sheet names without me typing them
though as there will be a large number of sheets.
 
M

Mike

The code below will list all the sheet names in you workbook to column B and
add the !

Sub getsheetnames()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Worksheets("Sheet1").Cells(x, 2) = ws.Name + "!"
x = x + 1
Next ws
End Sub

Mike
 
L

Lori

You could use insert>name>define "Sheets" refers to =get.workbook(1).
Then to get a list of sheet names, fill down from row1:

=INDEX(Sheets,row())

To remove workbook name: copy, Paste values and replace "]*" with
nothing.
Then fill down next to the sheet names the formulas:

=INDIRECT("'"&B1&"'A1")
 
P

pablo bellissimo

and as if by magic...

Thanks guys!!

Mike said:
The code below will list all the sheet names in you workbook to column B and
add the !

Sub getsheetnames()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Worksheets("Sheet1").Cells(x, 2) = ws.Name + "!"
x = x + 1
Next ws
End Sub

Mike
Click to expand...
 
D

Dave Peterson

I bet Gary''s Student meant:

=INDIRECT(B1 & "!A1")

but better (when the worksheet name needs to be surrounded by single quotes:

=INDIRECT("'" & B1 & "'!A1")
 
P

pablo bellissimo

Thanks to everyone for their help.

I downloaded a new utility pack which I used to create an index page listing
all the sheets and then used Gary's student's suggestion.

Not quite as dynamic as I would like but it works for now...
 
A

Ali

Hi Mike
I have exactly the same problem as Pablo. however, I would need my sheet
names to appear in Column S, so that I would use the formula =INDIRECT(S1 &
"A1")

i am new to macro's. have tried to follow the 'help' steps, but i get a
'subscript out of range error' when i run the macro. i would need to have
the macro run automatically whenever any-one accesses the workbook. is it
possible to help some-one who is backward with Macro's with some basic
instructions or is it beyond my scope. i have 70 sheets numbered 1-70 so in
column S I could write out 1!, 2!, 3! etc then the formula would work.
However, sheets are temporarily numbered 1-70, and as a safari runs, the name
is changed to a guest name, which means my column 'S' would later be of no
use as sheets would be called Keith!, james! pope! etc. many thanks Ali
 
Top