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
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.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.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.Finish the orders: in
E2, multiply the quantity by the price,=C2*D2, and copy it down to E6.
Remember
Sheet!A1reads a cell on another sheet;Sheet!A2:B5reads a range.- Start a formula, click the other sheet's tab, pick the cells, press Enter.
- A lookup table on its own sheet is written once and used everywhere.
- A name with spaces is written in quotes:
'Price list'!B2.
In the function reference: MIN, VLOOKUP
Previous: The total row · Next: Add, rename and link sheets