Auto Data Fill Question?

chetp

New Member
Joined
Sep 9, 2003
Messages
6
Auto Data Fill Question? Newbie

I have a workbook to invoice customers. In the workbook I have multiple invoice sheets, a totals sheet, and a custname sheet. The custname sheet contains columb headings of Cust#, CustName,Address,and city_st_zip.

My question is: May I fill in or look up from a list in a cell (say c7) on the customer invoice sheets and fill (C7)with the customer number (Unique) found in another sheet called (Custname). After excel fills in this information I would like excel then to fill in the custname, address and city_St_zip.

Sheet 1 called Custname
A1- Cust#
B1- Custname
C1- Address
D1- City_St_zip

Sheet 2 called Invoive

Cell on Invoice sheet C7 would lookup from a list or be entered manually the cust# found on Sheet Custname.

Excel would then find data from the Custname sheet and copy it to cells on Invoice sheet.

G10 would contain the Custname based on the Cust# found inC7
G11 would contain the address
G12 would contain the city_St_zip all based on custnumber in C7

I have excel 2002 but need this to work with Excel 97.

I am really new at this and it has been driving me nuts Thanks in advance.

Thanks in advance
Chet
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi - welcome to the board!

'Teach a man to fish' version of the answer...Check the help file for vlookup() & search the board for examples - sounds like just what you need. Post back if you can't get it working.
 
Upvote 0
I have literally sent hours searching for the answer to this question, that is how I found this great site. Maybe, if I knew what question to ask I could find the answer. Thanks for your reply. I want to enter a customer number in one cell on my invoice sheet and have excell look in a custname sheet and fill in automatically the cust name, address, city, st, and zip.
Thank you for your answer in advance.
chet
 
Upvote 0
What is it about the description of vlookup() in the help file that you don't understand?

Anyway:
Book2
ABCD
1CustNumCustNameAddressCity
21aho
32bIp
43cjq
54dkr
65els
76fmt
87gnu
9
10
11
12Find3
13Namec
14Addressj
15Cityq
Sheet1
 
Upvote 0
Thank You For your reply.

I can get VLookup to work as long as the formula is referencing info found on the same sheet it is domicile in. The problem I have is that the information I need is on another sheet in the same work book. I do not know how to reference that sheet I get Name? error. I really want to be able to enter a cust# in my invoice sheet and have excel look it up in a (different) sheet called Custname and return the custname, address, city, st, zip to be place into 5 different cells in the invoice sheet. This way when I enter a customer number the address is automatically filled out for mailing.
i.e.

On Invoice sheet
C7= Customer number (I will type this in.) This is the input cell.

I would like excel to go to the custname sheet look up the record (row) containing the customer number found on sheet called Invoice in (C7)and return the whole row or what amounts to one whole customer record

g10-customer name on Sheet called (Invoice) returned from Sheet called (Custname)
g11-customer address on Sheet called (Invoice) returned from Sheet called (Custname)
g12- customer city on Sheet called (Invoice) returned from Sheet called (Custname)
g13- customer state on Sheet called (Invoice) returned from Sheet called (Custname)
g13- customer zip on Sheet called (Invoice) returned from Sheet called (Custname)

This is probably as plain as mud,sorry.

Thanksfor your help I need it!

Chet
 
Upvote 0
Vlookup() is perfectly capable of referencing other sheets. rather than doing the typing yourself, use the formula wizard (the thing you get when you click on the equals sign next to the formula bar) & a point & click approach to selecting the ranges - excel will construct the right syntax.

failing that, at least post the formula you used that was returning errors.
 
Upvote 0
IF(C7="","",VLOOKUP(Customer!A2,Customer!$A:$F,1,FALSE)) is the formula in C8 which rederences C7 on sheet named invoice this returns an error.

I have a sheet called Customer. In it I have 5 columbs A-F, It has a named range of Cust!$A:$F. The headings read
A-Cust#
B-LastName
C-Name
D-Address
E-City_St_Zip
F-status
The first record starts in A2 with the first customer number.

ON another sheet I have a cell C7 in which I want to Type in a customer number not neccessarily the first number found in the Customer sheet.
From this number entered in C7 on the invoice sheet, excel should look up the customer name and place it Cell A66, then palce the address in A67
then place the city_st_zip in Cell A67, all of which are found on a Sheet called (invoice)

Can anyone help

As I said in the first line of this tread I am new to this. I have spend several hours looking for the answer so do not reply "Read The Book"

I have found thru my research that vlookup normally returns the result of a specific cell in a sheet. This is not what I am doing I want to enter a Customer Number in a cell on MY INVOICE sheet that can be found anywhere in Columb A of the CUSTOMER sheet. Match The Customer Number with a Customer Name and return the customer name to the sheet called IVOICE in Cell A66 which were it is to be stored. And so on until the address is complete. I do not think a Vlookup statement will do this by itself
IF I wrong blame it on being new.

Thank You in Advance
Chet
 
Upvote 0
An easy way to understand what I am doing( I just thoughout of this)
I need to enter a number on A shhet called Invoice

Have excel look a A columb in another sheet called Customer
Match the two Numbers

Bring the data (Name) found next to the number in the Table (Sheet) called Customer and return it to a different cell in this case A66

Can this be done

chet
 
Upvote 0
"I have spend several hours looking for the answer so do not reply "Read The Book" "

In general, I see no reason not to direct someone to a reference if I think it will help. In this case, a further review of the help file entry for vlookup() seems appropriate. Your formula:



IF(C7="","",VLOOKUP(Customer!A2,Customer!$A:$F,1,FALSE))


...indicates that you have not quite understood the intent of the 3rd argument (where you've got a 1).

The syntax for vlookup() is as follows:

=vlookup(find_what,find_where,return_from_which_column,exact_match?)

where:

1) find_what is the item you want to search for. Usually this is a value in a cell, so this is usually a reference to that cell. Given the data you have posted, you want to find data for a customer number that is in C7 in the invoice sheet. So, assuming your formula is aslo on the invoice sheet, it will start:

vlookup(c7...

2) find_where - this tells excel which range of cells to look in. Because of the way vlookup() is designed, the find_what item needs to be in the left most column of this range. As you indicated that custno was in column A of your customer sheet, this is fine. Now, while vlookup() will accept whole column references (A:F etc), it's overkill to get excel looking in 65536 rows if your data only occupies 500. Although there are methods for getting the find_what range to automaticallyfit the amount of data you've got, It's simpler to just use a range that's suitably larger than your dataset is likely to be. So, assuming for example that you've got 5000 lines of data that grow @ 100 lines a month, 8000 would give you a few year's slack. we now get:

=vlookup(c7,Customers!$A$1:$f8000...

It is important that the sheet name is exactly as it is on the sheet tab.

3) return_from_which_column - this tells vlookup which column you want data from. If your data range is cols a to f, and you want last name, which is in col b, then this is the second column in the range a:f, so this argument needs to be set to 2. So far:

=vlookup(c7,Customers!$A$1:$f8000,2...

4) exact_match? This tells the vlookup() whether to only find an exact match on the find_what value, or whether to settle for something close. Given the nature of this task, we clearly want only exact matches, so:

=vlookup(c7,Customers!$A$1:$f8000,2,FALSE)

or

=vlookup(c7,Customers!$A$1:$f8000,2,0)

...either will do as 0 and FALSE are frequently equivalent in excel.
 
Upvote 0
ThanK You So Much!!!! Now this makes sense
Do you realize that the avarage person Seeing VLOOKUP(B12,A1:D8,2,0)
Just scatches his or her head.
Now VLookup(find_what_,Find_where,Return_from_which_columb,exact match) makes sense even to me but it is also the first time I have found it presented this way.
Again thank you for you time and sharing your explanation.
chet
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top