How do I retieve/ copy data from another work sheet ?

[pete mann]

Hello all,

I have a question which is doing my health my damage

The question is this .. how do I retieve data from another workshee
just buying typing in a a name ?

I would ideally like to type in, say a suppliers name and then th
address would be retirved from another work sheet with the sam
spreadheet ?


PLEASE HELP ME !!!!!

 

[whisperer]

Assuming that the names that you want are in Column A of Sheet2 and the
address in Column B of Sheet 2, then in Sheet 1 B1 enter the following
code.

Code:
--------------------

=IF(A1="","",VLOOKUP(A1,Sheet2!$A$1:$B$5,2,0))

--------------------

Now replicate that formula down as far as you want to go. If you enter
a name in Column A it will then lookup the corresponding address in
Column B

To complete the sheet you ought to use a data validation list in Sheet1
ColumnA that matches the names in Sheet2

HTH

 

[pete mann]

Thanks for the response but I would think it would be better if
suplier you acopy of the spreadsheet that I am having trouble with.

If you can look at the 1st work sheet named 'Purchase order form', i
cellA8 I would like to type in a suppliers details and then it woul
retireve the following information from the suppliers details wor
sheet.

for example if I typed in ABC it would then retrieve the following fro
the suppliers details work book.

Supplier Name Street Town County P.O Code
ABC 1 1 1 1

I hope I have provided further explanation on what is require if no
please dont hesiate to contact me for further informatiom.

many thanks for your help
Pete Man

Attachment filename: spreadsheet design.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=39430

 

[whisperer]

Pete,

Sorry for the delay but we have been out visiting friends.

Nothing much changes, having seen the spreadsheet, the Vlookup scenario
is the easiest way forward.

I have assumed that you want all of the data to be in Row 8, if you
want it to appear below the titles then change the insertion to B10 et
seq, the formulae remain the same.

In B8 you would use

Code:
--------------------

If($A$8="","",A8)

--------------------

In C8 you would put the formula

Code:
--------------------

=IF($A$8="","",VLOOKUP($A$8,'Supplier Details'!$A$5:$F$20,2,0))

--------------------

In C8 you would have the same formula replacing the 2 with a 3 to
identify the column number that you wish to get the data from. You
would repeat this across to the telephone column

Change the A5:F20 reference to cover your suppliers.

HTH

 

[whisperer]

Pete,

Find attached a modified spreadsheet for your use

Gordon

Attachment filename: pete.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=394395

 

[pete mann]

HTH,

I am sorry for being so thick, but this rocket science is new to me at
my age. Could I ask you to provide me with the spreadsheet with it
done so that one can see how you have done it and if its still
okay.,ask you some further questions relating to this topic ?