Creating a master sheet which includes buttons with links to certain cells

Copper Contributor

I need some help and info on a file (master sheet) I'm creating. So I have inserted about 75 buttons to go to various cells within the master sheet. It's a monthly expense report and will go to each week within the month and various weekly accounting expense codes.  I have completed that part with no problem and everything works.

 

Here's my issue now. I take my master sheet and create 12 new sheets and rename each one Jan-Dec. One for each month. Now for example I'm in the worksheet for January and press the button to go to week 3 and the link will go to week 3 but to the master sheet. All the hyperlinks say mastersheet and will not update to the new name created. How can I work around that for a quick fix

 

 

3 Replies

@Mike_I0130 

 

Create an external reference (link) to a cell range in another workbook

 

Create a link to another worksheet

  1. Select the cell or cells where you want to create the external reference.

  2. Type = (equal sign).

    If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis. For example, =SUM(.

  3. Switch to the worksheet that contains the cells that you want to link to.

  4. Select the cell or cells that you want to link to and press Enter.

    Note: If you select multiple cells (=Sheet1!A1:A10), and have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER. For more information on array formulas, see Guidelines and examples of array formulas.

  5. Excel will return to the original worksheet and display the values from the source worksheet.

 

Hope I was able to help you with this info.

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE Thanks for response but I think you misunderstood what I was asking. So here's what I'm trying to do. 

 

1. Insert a shape and put on a blank excel file. We shall call this the master list

2.  On the box itself right click and go to link

3. Go to Place in this document 

4. In the box type the cell reference on top put the cell you want the button to go to

5.  Right below that it says select a place in this document and choose the worksheet thats your masterlist

6. Now make a copy of the worksheet and name it January.

 

Here is where I'm having my problem. If you click on the box to go to the cell you want it goes back to the master list not the worksheet named Januart bc the original link was created in the master. I have 76 buttons and I cant sit here and edit everyone for all 12 months it will take alot of time. Looking for a quicker way

@Mike_I0130 

In advance, with your permission, I would like to recommend an update and, if necessary, an office repair. At the same time, it would be very useful to know the Excel version, operating system, storage medium (OneDrive, hard drive, Sharepoint, etc.).

In addition, add a file (without sensitive data) or photos (if they help the task) and explain your request step by step on the basis of this file.

Thus you can get a faster and more precise solution proposal.

At the same time, the helper can focus the available time on the problem / topic without necessarily having to guess.

 

The file with the example is included, I don't know whether this will help, but it is worth a try :)).

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)