Saturday 27 March 2021

Excel Basics - Referring to Cells on Different Worksheets

Sometimes in Excel, you're working on one worksheet and want to refer to a value in a cell on a different worksheet.  By way of example, in this post I described using a spreadsheet with an exchange rate on it to convert from home currency to local currency when I am abroad.  Here's a quick image to remind you:


Let's say I'm abroad but on business, not on holiday and I want to keep track of personal and business spending on different worksheets.  I'd use the worksheet above for personal expenditure and call it "Holiday Spends" and also setup another one like the one below for business expenditure.  Note I've not put the exchange rate on the worksheet because I want to use the one on the "Holiday Spends" worksheet.
















A formula to use the exchange rate from the "Holiday Spends" sheet looks like this:
















So what can we see here?
  • The C9 means "use the value in cell C9 on this worksheet.
  • The * means multiply
  • The 'Holiday Spends'!$A$2 means use cell A2 from worksheet "Holiday Spends".  Remember the worksheet name goes in single quotes and there's an exclamation mark between the worksheet name and the cell reference.
Note that you don't have to type that formula out yourself.  You can build it up by:
  • Clicking in the target cell for the formula (so cell D9 in the above example).
  • Typing the "=" sign
  • Clicking on cell C9 of the current worksheet
  • Typing the "*" sign
  • Switching to the worksheet with the cell on it you want to refer to and clicking on the cell.
  • Pressing the return key
  • If required, editing the formula to make the cell on the other worksheet an absolute reference.

Hope that's clear!


No comments:

Post a Comment