Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a cell in another worksheet?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a cell in another worksheet?
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)) "Kevin" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a cell in another worksheet?
In B2 =SUMPRODUCT(--($A$1:$A$100=$A2),--(B1:100=B$1),$C$1:$C$100)
A2 = chair B1 = Black "Kevin" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a cell in another worksheet?
Thanks so much MrAcquire! :)
"MrAcquire" wrote: 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)) "Kevin" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a cell in another worksheet?
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. "Kevin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Text Cell to Reference a Worksheet Name | Excel Worksheet Functions | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
How to reference cell in other worksheet | Excel Worksheet Functions | |||
Can you reference a cell that is sorted in excel 2002? | Excel Worksheet Functions | |||
Cell Reference Math | Excel Worksheet Functions |