Excel - Relative references to cells in other worksheets in the same workbook - user question

excel logoThis is an answer to a user question. If you have a question you can ask it by using this form.

Terry asked the following question about Excel:


Subject: Relative workbook references
Question: Can you make relative references to other workbooks in the same Excel file?

I'm pretty sure Terry means other worksheats in the same file, as a workbook is an Excel file. So the answer here is yes, it's very simple.

I've set up a sample workbook, with 2 worksheets. I'm going to enter the numbers '1' and '2' into the first 2 cells in the sheet labeled numbers, highlight boths cells, move the mouse to the bottom right of the cells until it changes to the cross cursor, then drag down to automatically increment up to the number seven:


excel_worksheet_reference_01

excel_worksheet_reference_02

Now I'm going to the worksheet labeled days, and do the same except this time entering in Monday and Tuesday, then dragging down to fill the rest of the week:


excel_worksheet_reference_03

Now go back to the sheet labeled numbers, enter an equal sign into the formula bar, then click on the sheet labeled days:


excel_worksheet_reference_04

Click on Monday in the first cell, then hit the enter key:


excel_worksheet_reference_05/>
You should see this now on the sheet labeled numbers:


excel_worksheet_reference_06

Drag the cross cursor down to the 7 rows and it will fill in the values from the days worksheet:


excel_worksheet_reference_07

If you want to prove that it works go back to the days worksheet and change some of the values, you will see those changes reflected on the numbers worksheet.

Now, you can actually do the same to reference a different worksheet in a different workbook, too. Just have both workbooks open, have the target worksheet in the target workbook active, enter an '=' sign in the formula bar, then select the cell in the other workbook that you want. Hit the 'enter' key and you'll see the complete reference in the formula bar.

Just remember, if you do this the path to the other workbook is hardcoded into the cell, so if you move the other workbook the references will break. That, too, is relatively easy to fix, we'll cover that in another article.

Bytes: