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

What I mean is if (Sheet 2 column A row 7) has a value of "10", and on
(Sheet 1 column A row 7) I enter "10", I would like "ABC Trucking" to
be entered in (Sheet 1 column B row 7) and I would like "3:00 PM" to be
entered in (Sheet 1 column E row 7), etc. See attached spreadsheets as
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 |
+-------------------------------------------------------------------+

--
compconnj
------------------------------------------------------------------------
compconnj's Profile: http://www.excelforum.com/member.php...o&userid=32677
View this thread: http://www.excelforum.com/showthread...hreadid=524864

I think the formula you're looking for is VLOOKUP. This will work as
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 the
range A7 to Z1000; change the formula if it's different (e.g. replace
$A$7:$Z$1000 with $A$7:$FZ$10000). The $'s are important for then
copying the formula.
The formula basically looks up what you've put in A7 (which is 10) and
then finds the row this is on in Sheet2 and then looks at the value in
the same column (B in this example) for that row.
Copy the formula across and down. The columns will need to be the same
order in both sheets.
Clive

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

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


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=524864

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


--
compconnj
------------------------------------------------------------------------
compconnj's Profile: http://www.excelforum.com/member.php...o&userid=32677
View this thread: http://www.excelforum.com/showthread...hreadid=524864

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

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


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=524864