Course / Sheets and workbooks

Formulas that read another sheet

Keep a price list on its own sheet and use it from the orders: a cell, a range, and a VLOOKUP that reads another sheet. Intermediate · 6 min · Sheet reference, Sheet!A1, VLOOKUP across sheets

A workbook is made of sheets. Real workbooks keep each kind of thing on its own sheet: the orders on one, the price list on another. Then the price list is written once, and every formula that needs a price reads it from there.

A formula reads another sheet by putting the sheet's name and an exclamation mark in front of the cell: =Prices!B2 is cell B2 of the sheet called Prices. A range works the same way: Prices!A2:B5.

You rarely type the name. Start the formula, click the other sheet's tab, click or drag the cells you want, and press Enter: Excel writes the name for you and takes you back.

The sheets of a workbook are the tabs under the grid. Click a tab to show its sheet.

Steps

  1. The prices are on a second sheet. Click the Prices tab under the grid to look at it, then click Orders to come back. In B8, read the price of a desk from that sheet: type =Prices!B2.

  2. You don't have to type the name. In B9, type =MIN( and stop there. Click the Prices tab, drag over the four prices (B2 to B5), and press Enter.

  3. Now the real job: find each order's price. In D2, enter =VLOOKUP(B2, Prices!$A$2:$B$5, 2, FALSE) and copy it down to D6.

  4. Finish the orders: in E2, multiply the quantity by the price, =C2*D2, and copy it down to E6.

Remember

In the function reference: MIN, VLOOKUP

Previous: The total row · Next: Add, rename and link sheets