How to link to cells on different worksheets in Excel

With Excel used to create large reports you will often find a need to link a cell value on one worksheet to a different worksheet in the same file.  This is simple when you know how so read on to find out how to link cells on different worksheets in Excel…

 

One of Excel’s powerful features is the ability to link and combine information from different worksheets, there are many times when this comes in handy and not just in formula calculations.

For example if you have a monthly report which has the date on each worksheet you can change this so it all links to the cell containing date on the first worksheet, that way you only have to remember to update one cell as opposed to several, very handy if your report has a lot of worksheets.

Example

In the below image the report date is contained in Cell A1.  This is on the worksheet called “Sheet1”:

00054_LinkCellsOnDifferentWorksheets_01

 

 

 

 

 

 

 

 

 

 

To link that cell to the cell A1 on the worksheet “Sheet2” you write the following formula in cell A1 on “Sheet2”.

=Sheet1!A1

So in Excel it looks like:

00054_LinkCellsOnDifferentWorksheets_02

 

 

 

 

 

 

 

 

When you enter that formula, hey presto you will see the same thing on Sheet2 as you do on Sheet1, the cell is linked:

00054_LinkCellsOnDifferentWorksheets_03

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The formula in more detail

=Sheet1!A1

The formula in itself is a simple one to understand.  When you are working on the same worksheet you can refer to just the cell reference, i.e. you can just write a formula that says =A1.  However, when you are referring to a cell on a different worksheet you need to tell Excel what worksheet it is on, that’s where the Sheet1! Part comes into the formula.

Different Worksheet Names

When creating reports in Excel you will often change the name of worksheets to be more meaningful than the standard Sheet1, Sheet2 etc.  Where this is the case you use that particular sheet name in the formula.

For example if in the original example above Sheet1 was called Contents the formula would become

=Contents!A1

Spaces in Worksheet names

One occurrence that may cause you to stumble is when you have a worksheet name that contains a space in it, for example “Contents Page”.

In this case you cannot just change the formula to =Contents Page!A1 because Excel is not fond of blank spaces in formula references, it will not work and will return an error.

To work around this is simple.  You need to tell Excel that Contents Page is one string and to do this you put apostrophes surrounding the whole word, like so:

=’Contents Page’!A1

Now Excel will understand that you mean the worksheet called Contents Page and the formula will work and link the cells.

————————-

That is the basics of linking cells different worksheets in Excel, now you can go ahead and link cells with confidence.

Why not take that Excel knowledge to the next step and read about automating title dates from your data.

Keep Excelling,

Leave a Comment