Attributing the value of a cell based on another corresponding cell

Question

I have many files of the sort: Sheet1 and Sheet2.

Sheet1

Sheet2

Is it possible for a user to automatically copy a value they enter in, say, B2 in Sheet2 to B5 in Sheet1? As a result, when the user types something and presses Enter, B5 receives the updated value.

When using a fixed range of cells (Worksheets), I am able to do this (1).

Range("B1:B14").

worksheets as the value (2).

Range("B1:B14").

Value), although in this instance, Sheet1's range and Sheet2's range are distinct. Excel would first need to locate the relevant cell. How can I go about doing that? Although I'm sure it could be done without it, I'd much rather use VBA.

Answer 1

Put in cell B1 of Sheet1:

=INDEX(Sheet2!B:B;MATCH(A1;Sheet2!A:A;0))

Drag it down to all other Sheet 1 column B cells. I hope this helps you.