• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30
  • Last Modified:

EXCEL - Set a cell's value based on a value in another worksheet

I have a spreadsheet with 2 worksheets and am trying to set a value on one worksheet based on a value in the other worksheet using an excel formula. An example follows:

Worksheet 1 (WS1) has three columns (1) WS1-ROWID, (2) WS1-EXTERNALID, (3) WS1-DESCRIPTION, and (4) WS1-QUANTITY
Worksheet 2 (WS2) has two columns (1) WS2-ROWID AND (2) WS2-EXTERNALID

There is a one to many relationship between the two worksheets. For every row in WS2 there are 1 or more rows in WS1
The worksheets are related by the WS1-EXTERNALID and the WS2-EXTERNALID

I am trying to populate WS1-ROWID in WS1 with the value found in the corresponding WS2-ROWID. So, if WS1-EXTERNALID = WS2-EXTERNALID, set WS1-ROWID = WS2-ROWID

I probably didn't explain that real well but I've also attached a sample spreadsheet that should describe what I am trying to do.

Any help will be greatly appreciated.
1 Solution
KoenChange and Transition ManagerCommented:
is this what you need ?

write in Worksheet 1 , cell a2 : = index('worksheet 2'!$a$2:$a$5, match(b2,'worksheet 2'!$b$2:$b$5,0))
and copy down...
cbridgmanAuthor Commented:
Thanks Koen,

That is just what I was looking for.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now