How To Retrieve Data from Sheet2 into Sheet1

[compconnj]

What Function can I use to get data from Sheet2 into Sheet1 based o
input on Sheet1? IF function won't work because tables will be severa
hundred rows long. If a function can't be used, is there another way t
do it?

What I mean is if (Sheet 2 column A row 7) has a value of "10", and o
(Sheet 1 column A row 7) I enter "10", I would like "ABC Trucking" t
be entered in (Sheet 1 column B row 7) and I would like "3:00 PM" to b
entered in (Sheet 1 column E row 7), etc. See attached spreadsheets a
an example which should clarify what I mean. Thanks for your help

+-------------------------------------------------------------------
|Filename: Dispatch Database Test - Sheet 2.txt
|Download: http://www.excelforum.com/attachment.php?postid=4496
+-------------------------------------------------------------------

 

[Clivey_UK]

I think the formula you're looking for is VLOOKUP. This will work a
long as the numbers in column A (e.g. 10) are in order.
Try this in Sheet 1 column B row 7:
=VLOOKUP($A7,Sheet2!$A$7:$Z$1000,COLUMN(B2))
This assumes that (ignoring the headings) the data in Sheet2 is in th
range A7 to Z1000; change the formula if it's different (e.g. replac
$A$7:$Z$1000 with $A$7:$FZ$10000). The $'s are important for the
copying the formula.
The formula basically looks up what you've put in A7 (which is 10) an
then finds the row this is on in Sheet2 and then looks at the value i
the same column (B in this example) for that row.
Copy the formula across and down. The columns will need to be the sam
order in both sheets.
Clive

 

[compconnj]

Thanks for your reply. Initially, it worked but then I added a 3r
sheet and used data from the 3rd sheet as validation for one of th
columns that the VLOOKUP function referenced. When I did this, th
VLOOKUP only returned one value and not the corresponding value of th
correct row. Any ideas on how I can get around this issue? Is part o
the problem that I changed the order of the columns in Sheet1 so the
don't match the order on Sheet2? Thanks

 

[Clivey_UK]

As long as the rows are in increasing order in column A I'm not sure wh
it would return a value from a different row. Changing the order of th
columns would cause a problem but this can be rectified by changing th
last part of the formula. e.g. the part of the formula that say
column(b100) returns the value 2 as column B is the 2nd column. Th
vlookup therefore looks 2 columns over to get the result. So say i
looks up the value 100 and finds it in A8, it counts across 2 column
(including column A) which gives the value in B8.
Give me a bit more info about the validation you refer to, and also th
bit about 'only returned one value'.