Link to different tab on worksheet

[Susan]

I have created a link to sheet 1 in a workbook. I want to copy the link to the next row down, but I don't want it to go to the next cell down in sheet 1, I want it to go to the next worksheet in the workbook. For instance, if my link is to sheet1:a2, I want the copied next cell to be sheet2:a2. Is there a way to do this?

 

[Don Guillett]

Assuming you have the first formula in row 1, insert this and copy down

=INDIRECT("Sheet"&ROW()&"!$A$2")

I have created a link to sheet 1 in a workbook. I want to copy the link

to the next row down, but I don't want it to go to the next cell down in
sheet 1, I want it to go to the next worksheet in the workbook. For
instance, if my link is to sheet1:a2, I want the copied next cell to be
sheet2:a2. Is there a way to do this?

 

[Gord Dibben]

Susan

=INDIRECT("Sheet" & (ROW()) & "!A2")

Entered in top row of a column in your worksheet with the links.

Copy down as far as you have sheets.

Note: this assumes your sheets are named Sheet1, Sheet2, Sheet3 etc.

If something else, post back.

Gord Dibben XL2002

 

[alsweb]

I have the same question as Susan. Suggestion does not
seem to work. My sheets do have unique names. Is there
an alternative solution? Thanks, Alsweb

-----Original Message-----
Susan

=INDIRECT("Sheet" & (ROW()) & "!A2")

Entered in top row of a column in your worksheet with the links.

Copy down as far as you have sheets.

Note: this assumes your sheets are named Sheet1, Sheet2, Sheet3 etc.

If something else, post back.

Gord Dibben XL2002

to copy the link to the next row down, but I don't want it
to go to the next cell down in sheet 1, I want it to go to
the next worksheet in the workbook. For instance, if my
link is to sheet1:a2, I want the copied next cell to be
sheet2:a2. Is there a way to do this?

 

[Kevin Stecyk]

Alsweb,

The key lies in your comment, "My sheets do have unique names."

The formula given by both Don and Gord relies on the sheets have names,
Sheet1, Sheet2, Sheet3 etc.

=INDIRECT("Sheet" & (ROW()) & "!A2")

What the formula says is, go to Sheet(number-as indicated by row)) and cell
A2.

If you have sheets that are named Kansas, California, NewJersey, Montana
etc., then this formula above will not work.

Here is one possible way to address your unique named sheets

You need to modify the above formula to something like....

=INDIRECT("'"&VLOOKUP(ROW(),J1:K50,2,FALSE)&"'!A2") where J1:K50 is a lookup
table. In Col J, have the numbers 1-50, and in Col K, have the state names.
Then the formula will pick up the appropriate sheet name and concatenate it
with A2.

HTH

Regards,
Kevin

I have the same question as Susan. Suggestion does not
seem to work. My sheets do have unique names. Is there
an alternative solution? Thanks, Alsweb

 

[Gord Dibben]

Alsweb

If your sheet names(States as Kevin uses for example) are in column A you can
just enter the following and copy down.

=INDIRECT("'" &A1&"'" & "!A2")

If no chance of spaces in the names......

=INDIRECT(A1 & "!A2")

Gord Dibben XL2002

Alsweb,

The key lies in your comment, "My sheets do have unique names."

The formula given by both Don and Gord relies on the sheets have names,
Sheet1, Sheet2, Sheet3 etc.

=INDIRECT("Sheet" & (ROW()) & "!A2")

What the formula says is, go to Sheet(number-as indicated by row)) and cell
A2.

If you have sheets that are named Kansas, California, NewJersey, Montana
etc., then this formula above will not work.

Here is one possible way to address your unique named sheets

You need to modify the above formula to something like....

=INDIRECT("'"&VLOOKUP(ROW(),J1:K50,2,FALSE)&"'!A2") where J1:K50 is a lookup
table. In Col J, have the numbers 1-50, and in Col K, have the state names.
Then the formula will pick up the appropriate sheet name and concatenate it
with A2.

HTH

Regards,
Kevin

 

[Guest]

The unique sheet names (over 100 of them) are listed on
the worksheet tabs, not in a column. Short of typing them
into a column individually, is there any other way to
reference them, or to copy them into a column?

Thanks again,
Alsweb

 

[Kevin Stecyk]

Alsweb,

Create a sheet called "MyNames" and then run this small macro below.

Sub GetNames()
Dim wkSheet As Worksheet
Dim iCounter As Integer

iCounter = 1

For Each wkSheet In Worksheets
Worksheets("MyNames").Cells(iCounter, 1) = wkSheet.Name
iCounter = iCounter + 1
Next
End Sub


It will get each sheet name and place it in Col A in the tab (or sheet)
called "MyNames".

Hope that helps.

Regards,
Kevin

 

[Gord Dibben]

Kevin

We'll get this done eventually<g>

Guess I should have provided the same code with my last posting.

Gord Dibben XL2002

 

[Kevin Stecyk]

Gord,

<<We'll get this done eventually<g>>>

Thank you Gord. Actually, I enjoyed the small challenge.

Regards,
Kevin

 

[alsweb]

Kevin and Gord,

Thanks for your continuing support. I have printed out
your suggestion, and will input the macro as soon as
possible.

Thanks,

Alsweb

 

[Kevin Stecyk]

Alsweb,

You're welcome--Gord and I enjoyed helping. Let us know if you need further
help with this problem.

Regards,
Kevin