Help With Functions

[jksb]

I'm trying to get Excel 97 to do the following:
I want to be able to enter a company number in one cell; have Excel go to another workbook, lookup the number, then return the description to the next cell so that whoever is entering the number will see the item. I can make a seperate worksheet in the same workbook with just the #/item description if that's easier. I have gone into the Function Wizard, but am not sure just which one to pick...I tried HLookup but am not doing something right. In other words, if I type in 1001, I want to have (in the next cell), Heatwrap to appear.

Thank you for your help.

Jackie

 

[dsi]

OK. Lets see if I can explain this example. I know exactly what you are looking for, but since I do not know all of the details of your workbooks, I can not give you a clear cut solution.

WB1 - (C:\WB1.xls) - This is the workbook that has the master company list. The data is on a sheet named CustList. I will assume that the customer number is in column A and the customer name is in column B. I will also assume that there is additional data all the way out to column N. On top of this, I will also assume that the data starts on row 2, since the first row typically contains column headings. Lastly, I will also assume that there will never be more than 2000 companies in this list.

WB2 - (C:\WB2.xls) - This is the Workbook that will pull in the data when a company number is entered.
Cell A1 - this is where the company number is typed
Cell B1 should contain this function;

=VLOOKUP($A$1,'C:\[WB1.XLS]CustList'!$A$2:$N$2000,2,FALSE)

This function will take the value entered in cell A1 of WB2 (the customer number) and will search the CustList sheet of WB1 (data between A2 and N2000). If that value is found, it will return the data located in the second column for that entry. The FALSE indicates that the list being searched does not have to be sorted. A value of TRUE will require that the data on the JobList is sorted in ascending order. It may be a little bit faster if it was sorted, but should not be critical in your application. I assume this because you are using Excel over a database.

Hope this helps.

 

[JrClown]

jksb,
Here's how is done bud.

say you have on sheet1, cell a1 the word "Clown"
then on sheet2, cell A1 type this code
='sheet1'!a1
this will copy whatever you copy from sheet1 a1 to sheet2 a1

Then, on sheet2 a2 you type this
=if(a1="clown","Heatwrap","&quot
Obviously, this will automatically show the word "Heatwrap" in cel a2 when the word "clown" appears on cel a1.

If for some reason you still don't understand bud, email me at Jr_Clown@yahoo.com and I'll be happy to send you a sample of what I mean. Let me know... QUOTE OF THE DAY
I'm a firm believer in the theory that people only do their best at things they truly enjoy.

Jr_Clown )

 

[jksb]

First of all, thank you Big Time for this help. I really appreciate it.

Second, I am getting #N/A in column 2. I adjusted the function this way (I decided to make a worksheet with the info on it, as I have to send this out to 5 different warehouses. This is how I worked it:

=VLOOKUP($A$2,'Inventory Control'!$A$3:$B$3,3,FALSE)

Worksheet #1 contains 4 columns...#1 is the CompanyItem#, #2 is the ManufacturerItem#, #3 is the Description, #4 is the ManufacturerName.

Worksheet #2, the one I want to use for data entry, contains
5 columns...#1 is CompanyItem#, #2 is Description, #3 is CrewIssuedTo, #4 is #ItemsIssued, #5 is #InTruckStock.

What I want is for when the crew types in the CompanyItem# for the Description to appear in column #2.

Any help you can give is much appreciated. BTW, I will be using Access, but I had a deadline and found I could get a decent basic inventory set up in it faster, plus the warehouse people I am working with are familiar with Excel.
However, Access has the "bells & whistles" I want to use, so as soon as I get this up and running, I'll be tackling Access I'm 90% there...this is the last part of what I need to do with it for now.

Thanks again!

Jackie

 

[jksb]

OKay, Clown, now I will have to go try this one, too!

Thanks!

Jackie

 

[JrClown]

Would it be too much if I ask you to send it to me? I would like to see it and perhaps show you the code sample. Don't send any important information, (I don't care) I'm just trying to visualize the workbook. QUOTE OF THE DAY
I'm a firm believer in the theory that people only do their best at things they truly enjoy.

Jr_Clown )

 

[jksb]

Okay, Clown, tried what you suggested. Three problems...first, I can't get the formula to drag for the entire column in the first (='FirstWorksheet'!A3). It just copies the value. If I manually enter each one, it keeps it, but I have 300 items in inventory. Second, when I delete the number (as if I made a mistake) the entire formula goes bye-bye. Third, in the second column, where I want the description to show, if I leave it blank, False appears. I want the columns to stay empty until the crew enters in the items they used for that day. I'm sure the first two problems may be easy to fix, but would appreciate your help with it. BTW, the formula will drag in column 2.

Thank you...
(btw, I am a "Bud-ette", not a "Bud&quot

Jackie

 

[jksb]

Sure, what's your email...I'll send it tomorrow.

Thanks!

Jackie

 

[JrClown]

My apologies for not paying carefull attention to your signature of Jackie, that should've flagged me not to call you buddy. Sorry :-(

Back to your question:
1. By typing this =if(a1="clown","Heatwrap","&quotthe last 2 "" will show an empty cell the word "FALSE" shows when these 2 "" are not used in the formula.

2. Once you type the code on one cell, click on that cell and point at the bottom right corner where a very small square is located, point your mouse over it untill the cursor becomes a + than hold the LEFT mouse button and drag as far as you want the code to go. Let me know Jackie.


QUOTE OF THE DAY
I'm a firm believer in the theory that people only do their best at things they truly enjoy.

Jr_Clown )

 

[jksb]

Good Morning.

First, I know about the drag. It just won't work in my first row, and only sometimes.

Second, I had a problem with using quotes instead of apostrophes (except for the last two). Using quotes did the trick on the end. I kept getting formula errors when I used the quotes.

Third, it only works in row 1. I did drag it, the formula looks great, but won't work. I am also having a problem with col 1 and the ='sheet1'a1...it wants to put the info in the column, and I don't want it there unless the materials are issued.

Fourth, on some days some items will be issued and others not; it seems with this program the actual cell & numbers have to match for it to bring up the description. With the one working row, when I type in a different number then the one that matches exactly, I get nothing. It seems it needs to search the entire column and then bring up the correct description, which heads me back to Vlookup again.

Does this make sense?

Thanks!
Jackie

 

[JrClown]

The part that does not make sense to me is that it would work on some cells and not others.
If I can see the document, I could draw a better conclusion of what you are trying to do.
you can email me at Jr_clown@yahoo.com

QUOTE OF THE DAY
I'm a firm believer in the theory that people only do their best at things they truly enjoy.

Jr_Clown )

 

[jksb]

First, thank you for all your help. I still have questions on this and why it won't work...

<<<<<Second, I am getting #N/A in column 2. I adjusted the function this way (I decided to make a worksheet with the info on it, as I have to send this out to 5 different warehouses. This is how I worked it:

=VLOOKUP($A$2,'Inventory Control'!$A$3:$B$3,3,FALSE)>>>>>


Why is this not working? From my little knowledge, it should. Can anyone help?

And on this one:

=IF(A2='Inventory Control'!A3,'Inventory Control'!C3,&quot;&quot


Why/how can't I tell it to got to column 1 and do a vlookup to find what I want? The problem I was running into is that is *seems* to be only looking in the one row of column 1, not the whole thing.

Thank you! I really apprecate the help, even being told why it won't work.

Jackie