Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get a value for a different cell using one cell on the same row?
I hope that makes sense. Here's what I want to do.
I have Sheet1 and Sheet2. Sheet1 has... A B C 1 Route# Route Mileage ------------------------------------- 2 1 A-B 10 3 2 B-C 20 ........ 16 15 Y-Z 30 Sheet2 has... A B 1 Truck# Route#/Mileage ------------------------ 2 1 1 3 Formula? 4 2 4 5 Formula? I want to reference Sheet1 in Sheet2 so if I enter a Route# in Sheet2's cell, it'll associated this number with the Route# in Sheet1, and get the corresponding Mileage from Sheet1. So the Sheet2 cells (with Formula? above) I want to do this on will have this pseudo-formula, just to illustrate what I want. =if(Sheet2!C1=Sheet1!A2,Sheet1!C2,0) OR if(Sheet2!C4=Sheet1!A3,Sheet1!C3,0) OR ............ if(Sheet2!C14=Sheet1!A2=15,Sheet1!C6,0) Make sense??? -- - Zilla (Remove XSPAM) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get a value for a different cell using one cell on the same
If I'm reading this right and have understood you, I think what you need on
sheet 2 is a VLOOKUP() here's what it would look like in Sheet2!B3 (where you have the first Formula? entry shown) =VLOOKUP(B2,Sheet1!$A$2:$C$16,3,0) That should return the mileage from column C on sheet 1. B2 is the 'what to lookup', Sheet1!A2:C16 is a table to search in, with possible matches to value in B2 listed in the first column of that table. ,3, tells it to return value from 3rd column of the table if a match is found, and ,0) says that the items in the search column (A) don't necessarily have to be in order. Hope this helps some. "Zilla" wrote: I hope that makes sense. Here's what I want to do. I have Sheet1 and Sheet2. Sheet1 has... A B C 1 Route# Route Mileage ------------------------------------- 2 1 A-B 10 3 2 B-C 20 ........ 16 15 Y-Z 30 Sheet2 has... A B 1 Truck# Route#/Mileage ------------------------ 2 1 1 3 Formula? 4 2 4 5 Formula? I want to reference Sheet1 in Sheet2 so if I enter a Route# in Sheet2's cell, it'll associated this number with the Route# in Sheet1, and get the corresponding Mileage from Sheet1. So the Sheet2 cells (with Formula? above) I want to do this on will have this pseudo-formula, just to illustrate what I want. =if(Sheet2!C1=Sheet1!A2,Sheet1!C2,0) OR if(Sheet2!C4=Sheet1!A3,Sheet1!C3,0) OR ............ if(Sheet2!C14=Sheet1!A2=15,Sheet1!C6,0) Make sense??? -- - Zilla (Remove XSPAM) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get a value for a different cell using one cell on the same row?
Hi
Try =IF(COUNTIF(Sheet1!B:B,B2),VLOOKUP(B2,Sheet1!B:C,2 ,0),"") -- Regards Roger Govier "Zilla" wrote in message ... I hope that makes sense. Here's what I want to do. I have Sheet1 and Sheet2. Sheet1 has... A B C 1 Route# Route Mileage ------------------------------------- 2 1 A-B 10 3 2 B-C 20 ....... 16 15 Y-Z 30 Sheet2 has... A B 1 Truck# Route#/Mileage ------------------------ 2 1 1 3 Formula? 4 2 4 5 Formula? I want to reference Sheet1 in Sheet2 so if I enter a Route# in Sheet2's cell, it'll associated this number with the Route# in Sheet1, and get the corresponding Mileage from Sheet1. So the Sheet2 cells (with Formula? above) I want to do this on will have this pseudo-formula, just to illustrate what I want. =if(Sheet2!C1=Sheet1!A2,Sheet1!C2,0) OR if(Sheet2!C4=Sheet1!A3,Sheet1!C3,0) OR ............ if(Sheet2!C14=Sheet1!A2=15,Sheet1!C6,0) Make sense??? -- - Zilla (Remove XSPAM) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get a value for a different cell using one cell on the same
Exactly what I was looking for, TY!!!
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... If I'm reading this right and have understood you, I think what you need on sheet 2 is a VLOOKUP() here's what it would look like in Sheet2!B3 (where you have the first Formula? entry shown) =VLOOKUP(B2,Sheet1!$A$2:$C$16,3,0) That should return the mileage from column C on sheet 1. B2 is the 'what to lookup', Sheet1!A2:C16 is a table to search in, with possible matches to value in B2 listed in the first column of that table. ,3, tells it to return value from 3rd column of the table if a match is found, and ,0) says that the items in the search column (A) don't necessarily have to be in order. Hope this helps some. "Zilla" wrote: I hope that makes sense. Here's what I want to do. I have Sheet1 and Sheet2. Sheet1 has... A B C 1 Route# Route Mileage ------------------------------------- 2 1 A-B 10 3 2 B-C 20 ........ 16 15 Y-Z 30 Sheet2 has... A B 1 Truck# Route#/Mileage ------------------------ 2 1 1 3 Formula? 4 2 4 5 Formula? I want to reference Sheet1 in Sheet2 so if I enter a Route# in Sheet2's cell, it'll associated this number with the Route# in Sheet1, and get the corresponding Mileage from Sheet1. So the Sheet2 cells (with Formula? above) I want to do this on will have this pseudo-formula, just to illustrate what I want. =if(Sheet2!C1=Sheet1!A2,Sheet1!C2,0) OR if(Sheet2!C4=Sheet1!A3,Sheet1!C3,0) OR ............ if(Sheet2!C14=Sheet1!A2=15,Sheet1!C6,0) Make sense??? -- - Zilla (Remove XSPAM) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |