How to link cells in two worksheets in a flexible way

Peter Mueller 20 Reputation points
2026-10-15T12:10:19.37+00:00

I have two worksheets in an Excel document. Worksheet 1 is a stocklist with item names in Column A and their prices in Column B. The entries are sorted alphabetically by Column A.

I would now like to make names and prices of a few selected items in the stocklist appear on Worksheet 2. I know this is very easy to do by entering ‘=’ in a cell in Worksheet 2 and then simply clicking on the cell in Worksheet 1 from which I want the entry displayed.

HOWEVER, the stocklist might change over time. Some entries might be deleted and others added. Still being sorted alphabetically, some entries will therefore move and appear in different cells. For example, the item and its price from cells A12/B12 might now be in A9/B9 (Worksheet 1).

Is there a way to link the cells in Worksheet 2 to the data in their corresponding cells in Worksheet 1 so that the data does not change?

User's image

Because items have been removed from Worksheet 1, ‘steel sheet, cut’ has moved from A4/B4 to A3/B3 and ‘aluminium sheet, cut’ from A8/B8 to A6/B6. In Worksheet 2, however, the entries have remained unchanged ==>

User's image

That is what I want to achieve. Can it be done?

Microsoft 365 and Office | Excel | For home | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Barry Schwarz 6,271 Reputation points
    2026-10-15T17:35:42.4433333+00:00

    In column A of sheet 2, enter the names of the items you are interested in any order you like. In column B next to the first entry in column A, enter the formula

    =XLOOKUP(A1,Sheet1!A$1:A$12,Sheet1!B$1:B$12,"None",0,1)

    After confirming the result is correct, copy this formula down to the last entry in column A.

    Changes on sheet 1 will be reflected on sheet 2 whenever that sheet is recalculated (usually automatically). There is no need for the entries in column A of either sheet to be sorted.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.