Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2006

Tip: Looking for answers? Try searching our database.

Reference a cell in another worksheet?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Kevin - 07 Nov 2006 20:04 GMT
How do I populate a cell using another worksheet as a reference?

For example, if Sheet1 looks like this:

    A    B    C
1     Part    Color    Price
2    Desk    Black    200.00
3    Chair    Black    100.00
4    Lamp    Gold    50.00

Sheet2 looks like this:

    A    B    C
1        Black    Gold
2    Chair    100.00    150.00
3    Lamp    35.00    50.00
4    Desk    200.00    300.00

I want column C in Sheet1 to pull the price depending on the two crietria.

Any ideas?

Thanks.

Kevin
MrAcquire - 07 Nov 2006 20:38 GMT
Enter the following formula on Sheet1 in cell C2 and then copy & paste it to
C2:C4...

=INDEX(Sheet2!$A$1:$C$4,MATCH(Sheet1!$A2,Sheet2!$A$1:$A$4,0),MATCH(Sheet1!$B2,Sheet2!$A$1:$C$1,0))

> How do I populate a cell using another worksheet as a reference?
>
[quoted text clipped - 21 lines]
>
> Kevin
Kevin - 07 Nov 2006 20:51 GMT
Thanks so much MrAcquire!  :)

> Enter the following formula on Sheet1 in cell C2 and then copy & paste it to
> C2:C4...
[quoted text clipped - 26 lines]
> >
> > Kevin
Teethless mama - 07 Nov 2006 20:41 GMT
In B2 =SUMPRODUCT(--($A$1:$A$100=$A2),--(B1:100=B$1),$C$1:$C$100)

A2 = chair
B1 = Black

> How do I populate a cell using another worksheet as a reference?
>
[quoted text clipped - 21 lines]
>
> Kevin
Kevin - 07 Nov 2006 22:13 GMT
Oh, and what if the scenario involved pricing for different territories?  
Let's say there are ten territories and each territory has its own worksheet.
For example, "Territory1," "Territory2," "Territory3," etc.  The "part" and
"color" would be the same for each territory, but the "price" would vary.  
How would the function change to accomodate this?  Thanks.

> How do I populate a cell using another worksheet as a reference?
>
[quoted text clipped - 21 lines]
>
> Kevin
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.