moving data across sheets
Posted by Bob on March 16, 2001 7:25 AM
I'm trying to crate a spread sheet where i can select data from another sheet.
On sheet 1 i have five collums with customer data,
a is the customer number
b the account name
c the address
d the post code
and d the phone number
I would like to be able to enter a customer number on the next sheet and it brings up the whole line of data from sheet 1. can you help?
thanks
bob
Posted by kristel k on March 16, 2001 7:35 AM
you can use the VLOOKUP FUNCTION to get the information field by field.(so you 'll have to enter the function 4 times)
Posted by Michelle on March 16, 2001 7:41 AM
VLookup would be your best bet. Depending on whether you want it as one long string in a single cell or as independent sections of data in multiple cells, you could format it as you wish.
Format:
Vlookup("cell you are looking up" (example: the company name),the range of where the data is (example: the first sheet), what column you want to return)
If you have company names that are similar you might want to add a ,FALSE to the end of the expression prior to the last parentheses. That will insure that the company name is matched exactly. Otherwise it will default and return a close match
If you do not want a "N/A" if the company is not found you may want to combine it with a IF an ISERROR statment to simply return nothing.
Hope this helps.
Posted by Aladin Akyurek on March 16, 2001 8:03 AM
Select all of the cells in which you have the customer data, excepting the labels like customer name,etc and name this range, say CUSTOMERS,via the Name Box.
On sheet 2, select a cell, say A1, and enter the following:
=VLOOKUP(an-existing-customer-number, CUSTOMERS,{1,2,3,4,5},0)
Activate A1 and select also B1, C1, D1, and E1. Go to the formula bar, where you see the formula in A1, and hit CONTROL+SHIFT+ENTER at the same time.
This action will result in producing all the data related to the customer number, including the customer number itself.
Aladin
