when you click on a cell which jumps to another cell

Four · May 18th, 2002, 01:06 PM

I want to know how to enable me to click on a button on a spreadsheet and it will then jump to a specified cell.

stevebausch · May 18th, 2002, 01:22 PM

I suspect that

Cells(ThisRow,ThisColumn.Select

would be adequate.

Create the macro, assign macro to button.

Test the button.

Nimrod · May 18th, 2002, 01:58 PM

The "name Box" on your tool bar will also allow you to type in any Cell reference or range and it will go there as well.

Brian · May 18th, 2002, 03:55 PM

Check out the hyperlink function.

When A5 contains a sheet name:
=IF(ISERROR(INDIRECT(A5&"!b2")),"-",HYPERLINK("["&SUBSTITUTE(LEFT(CELL("filename",A5),SEARCH("]",CELL("filename",A5))),"[","",1)&A5&"!A1",INDIRECT(A5&"!b2")))

Links to the cell in column A that matches cell A2:
=HYPERLINK("["&SUBSTITUTE(LEFT(CELL("filename",A1),SEARCH("]",CELL("filename",A1))),"[","",1)&"index!A" & MATCH($A$2,Index!$A:$A,0),"{ INDEX }")

You don't need to make it that complex, however, the use of cells("filename",A1) was the only way I found to create a link that did not break when then filename or sheet name was changed.

May 18th, 2002, 01:58 PM	#3
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	The "name Box" on your tool bar will also allow you to type in any Cell reference or range and it will go there as well. __________________ <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 18th, 2002, 01:06 PM	#1
Four New Member Join Date: May 2002 Location: Four Posts: 1	I want to know how to enable me to click on a button on a spreadsheet and it will then jump to a specified cell.

May 18th, 2002, 01:22 PM	#2
stevebausch Board Regular Join Date: May 2002 Posts: 809	I suspect that Cells(ThisRow,ThisColumn.Select would be adequate. Create the macro, assign macro to button. Test the button.

May 18th, 2002, 03:55 PM	#4
Brian Board Regular Join Date: Apr 2002 Posts: 113	Check out the hyperlink function. When A5 contains a sheet name: =IF(ISERROR(INDIRECT(A5&"!b2")),"-",HYPERLINK("["&SUBSTITUTE(LEFT(CELL("filename",A5),SEARCH("]",CELL("filename",A5))),"[","",1)&A5&"!A1",INDIRECT(A5&"!b2"))) Links to the cell in column A that matches cell A2: =HYPERLINK("["&SUBSTITUTE(LEFT(CELL("filename",A1),SEARCH("]",CELL("filename",A1))),"[","",1)&"index!A" & MATCH($A$2,Index!$A:$A,0),"{ INDEX }") You don't need to make it that complex, however, the use of cells("filename",A1) was the only way I found to create a link that did not break when then filename or sheet name was changed.