By typing specific text into cell A1, it will know what goes in cell B1

[lukasj13]

Here is what I am trying to accomplish...

I am creating a spreadsheet that when I type the product series in cell A1, it will automatically know to enter the corresponding catalog # into cell B1. I will be doing this for hundreds of product series.

For Example:

If I type AEP into cell A1, the worksheet will automatically enter #123 into cell B1,

If I type DEB into cell A2, the worksheet will automatically enter #458 into cell B2,

and so on...


I understand that I will have to create a master list with what series goes with each catalog #, but I am not to sure where to go from there???

Please Help..............thanks in advance

[Portuga]

One way is to use use a vlookup formula.

In one sheet create the total list of codes in column A and its corresponding numbers in the next.

In your original sheet, you can then use a Vlookup formula to search the values in column A in the sheet where you have the list and return the values in column B.

[lukasj13]

Thanks for the reply, I will try that method right now. If anyone has a different method, I am still open to other suggestions as well.

[Portuga]

Vlookups can fail though, if you misspell your code in the cell, in which case, you will get a #N/A error message.
This message also appears if it doesnt fint the code.

You can get rid od the error message and display the text you want in case this happens.
(see attached)


Or you can get rid of the possibility of misspell by creating a named range from your list and use it in the cell validation - "List" option

[lukasj13]

Unfortunately I don't think that vlookup will work properly for this situation, then again I am not totaly good with it. This spreadsheet will be distributed to each salesmen and will inturn keep track of what catalogs they have given to each company, as well as each individual contact at these companies. With all of that said here is what I am working on, please take a look and tell me if there is a better way. All of the entries on the sheet I have done by hand, and as you can see there will be duplicate catalogs, but for different companies. Instead of talking your ear off I will just let you take a look.


Excel 2003

[Portuga]

Vlookup is the way to go. And so is creating a list of UNIQUE entries and codes otherwise the vlookup wont work properly.

I created a named range for you that will allow the cells in column E come from a dropdown list. Keep that list updated and and you shouldn´t have a problem.

If you have more codes, just add them to the list in the sheet and the dropdown will be automatically updated. Good if you keep the list sorted.

See attached

[lukasj13]

Thanks for taking the time to help me, I owe you one.

[lukasj13]

I am having some trouble understanding the formula in the sheet, as well as having trouble updating the list. Where is the list located at, that the drop down list is refering to. If you wouldn't mind, how would I complete the VLOOKUP in the formula wizard...

Lookup_value: ?

Table_array: ?

Col_index_num: ?

Range_lookup: ?

Whenever I do a drop down list, I normally hide it within the sheet and when validating I would choose list and set the range of cells. I am baffled as to how the validation that you set up works.

Again thank you for taking the time to help me with this, I guess I don't know that much about excel, besides the basics.

[Portuga]

Hi,

The list is located in the other sheet called "Lists". I created a named list using the following formula:

=OFFSET(Lists!$A$2;0;0;COUNTA(Lists!$A:$A);1)
(You can see this in "Insert" tab "Name", "Define")

What this does is create a named list based on columnA of sheet "Lists", whenever there is data in it. (I called it "List")

In the validation cell of column E, I just need to add =List.

It now allows values from columnA from sheet "Lists" that have values. If you want to add more codes to the list, just write them on columnA of sheet "Lists" and it will automatically be added to the dropdown list. (You should sort these columns alphab, otherwise the new value will always be at the bottom of the dropdown.

As for the vlookup its a combination of an if statement + iserror + vlookup so I dont really know how to tell you how to do this with the wizzard.

What is saying is If value in columnE is "blank", then return "blank", otherwise look for the value in sheet "Lists", range $A$1:$B$5000. If its not there, return "Code not found", if its there, return the value associated to the code in column 2 $A$1:$B$5000;2;

[lukasj13]

I will take a second look at it, thanks for breaking that down for me. I will let you know how it goes.

[lukasj13]

Each salesmen will have their own copy of the catalog system workbook. So, is there a way that I can make the list in a seperate workbook, and put it on the local drive within our network, so that everyone's catalog system workbook will has access to it. That way when I update it on the list workbook, their list will automatically update on their own copy of the catalog system workbook. Basically I just need the LIST within their woorkbook to reference a link to another workbook called LIST. Does this make any sense? If not I can try to explain a little better.

[Portuga]

In guess you could insert in each salesmabn copy the named list referring to your "list" spreadsheet.

=OFFSET(Lists.xls!$A$2;0;0;COUNTA(Lists.xls!$A:$A);1)

and use the validation as before, but I´m not sure if this would work properly in a network environment. Also, the "Lists" document would have to be open every time otherwise it wouldn´t work.

I would be a lot safer and problem proof if you just sent the new items to the salesman and they would insert it themselves in their sheet.

[lukasj13]

Thank you so much for your help with this. I will try to have them insert the list each time I update it. This has the possibility to cause a problem, because they don't all know excel that well(not even enough to insert a sheet), that and they are kind of lazy. I was just trying to make this idiot proof, oh well. Like I said, thank you soooooooo much for your help, I don't know what I would have done without it.

P.S. I got the lists.xls working within our network, is there a way to make a macro that will automatically open Lists.xls, every time the catalog organizer workbook is opened? If not thats ok, but I just thought I would give it a shot.




THANKS!!

Lukas

[Portuga]

Hi, You can try the following in each salesman spreadsheet:

Please Login or Register  to view this content.

You must go to the VBA editor (ALT+F11) click on "This Workbook" (of each one) and paste the code above.
It will automatically open the "lists" document whenever the sheet with this code is opened.

Obviously it must be updated with the network location of where your Lists spreadsheet is located.

Not sure what will happen if 2 salesman try to open it at the same time though.

[lukasj13]

That work's great, I will let you know what happens when 2 people open it. As far as updating the links seems kind of like a pain, I did it for one and it took a little while for our server to react. That may take awhile for each salesmen to do this. But all and all thanks for all of your help with this on going project, I'm not sure where I would be without your help. So thanks again.

[Portuga]

No worries Lukas,

Glad I could help.

(Performance wise, it might help if all documents are within the same shared folder in the network)

[lukasj13]

I thought that I would have to update each persons, but beacause I have it open up the lists.xls each time it does it for me....

Project Complete!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Thanks.

[lukasj13]

I have been trying to troubleshoot this for days, continuing to have problems.

Can you please test with lists in seperate wookbook?

and see if validation still works?

[lukasj13]

Sorry attatchment didn't work?

password = organize

[Portuga]

Hi Lukas.

Save the documents as excel 2003 otherwise I wont be able to see them.

(And read what I wrote in post 12)

[lukasj13]

I just saved this as a sinlge workbook. I was looking to have the Contact Catalog Organizer reference, Lists.xls, like we previously disscussed.

Again I appreciate you continuing to help me.

Thanks,

Lukas

[Portuga]

The ofset formula of the document will depend on the location you have your lists xls file, as the formula will be referring to that file.

Try defining the "name" with the Lists.xls open and actually clicking on cell a2 of "lists" xls for the first part of the formula and use the same for the second part (Counta)

Attached are 2 files that worked perfectly in my machine.

[lukasj13]

After messing with it some more, I finally got it to work!. I had it working one time before when you first gave me the formula, but I deleted the workbook because it wasn't the original and I wanted to apply it to the original workbook. I thought this wasn't a big deal because I had it working, and thought that I knew how to make it work. After that I couldn't get it to work again. At first I was just typing List.xls like you said, then I tried typing the full address, but I didn't know that I had to reference the workbook with [ ]. I relized I needed to do this when I was applying the list formula to another project that I was working on, where the list would stay inside the sheet. Suddenly there was the formula I needed, with the full address in it. (?) I then copied that address, deleted the original Defined List and created a new Defined LIST with the proper address for the Catalog Organizer list.

L:\Chicago\Catalogs\Contact Activity Organizer\00000[Lists.xls]Lists!

Once I entered that and hit ADD, it updated it to

=OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A),1)

then it worked, you've got me?

I just need to update everyones links, or tell them the path for the list, and it's all good.

Hope this made sense, I thought that I should try to explain as much as possible.

Anyways, thanks again for all of your help.

[Portuga]

I´m glad you managed to get it to work.

The formula willl automatically update with the location of the file.
If the file is open, the formula wont display the location but if its closed then it will. If you change the location of the file, the formula should also update with the new location.

[lukasj13]

Now, when I open the Contact Catalog Organizer.xls, it will then automatically open Lists.xls.

Which I want....

How can I get it to automatically go back to Contact Catalog Organizer.xls as the active workbook?

I have been trying this but with no luck.

This is in Lists.xls "thisworkbook"

Please Login or Register  to view this content.

Am I at least close???

Let me know

Thanks,

Lukas

[Portuga]

Try adding this line of code to the original macro.

Please Login or Register  to view this content.

As the file is already open, you dont need to open it again, just swich windows.

[lukasj13]

I knew that since it was open all that it needed to do was select the workbook, or window, but I still can't get it to work.

You may have to be a little more specific

What workbook to add code, where at in workbook, and full code such as Private sub or sub.


Please let me know.

Thanks,

Lukas

[lukasj13]

I have successfully created a macro to do this, but I can't make it do it automatically???

I have the module in workbook Lists.xls

Is there a way to make this in the "thisworkbook" ?

[Portuga]

Add that line of code to de ORIGINAL macro in the Contact Catalog Organizer.

Please Login or Register  to view this content.

[lukasj13]

This is what I have, and I can't get it to work, it keeps bringing up the debugger

This is in the Lukas Contact Catalog Organizer.xls "ThisWorkbook"

Please Login or Register  to view this content.

?????

Thanks,

Lukas

[Portuga]

If you click "debug" which is the Yellow row of code?

What is the exact name of your catalogue document?

[lukasj13]

I have been fixing the code whenever you give it to me, to the correct address, or file name.

Lukas Contact Catalog Organizer.xls

Here is what is in yellow...


Windows("Lukas Contact Catalog Organizer").Activate


And here is the full code in Lukas Contact Catalog Organizer.xls "ThisWorkbook"

Please Login or Register  to view this content.

The funny thing is that Windows("Lukas Contact Catalog Organizer").Activate
worked when I used it as a macro/module with the keyboard shortcut CTRL+T. But this was in Lists.xls instead, I thought it would have to be in this workbook, because once Lists.xls opens, it is then the active workbook. I have also tried the same code in the Lists.xls "thisworkbook" and it didn't work automatically.

I don't get it???

Thanks,

Lukas

[Portuga]

Dont get it either.

Is the macro in the Lukas Contact Catalog Organizer.xls document?

If you close both files and open Lukas Contact Catalog Organizer.xls
Is it opening lists.xls and getting the debug error?

[lukasj13]

Is the macro in the Lukas Contact Catalog Organizer.xls document?

Yes

Is it opening lists.xls and getting the debug error?

Yes

[Portuga]

Create a macro in lists.xls.

Have the following code there:

Please Login or Register  to view this content.

And run it. Do you get the error message?

[lukasj13]

There is no error, but nothing seems to happen???

Lukas

[Portuga]

Then my guess is that either you misspelt the file name, something in the network environment is causing this, or the command has changed in excel 2007. It works perfectly in my machine.

[lukasj13]

I'll keep messing with it for a while.

Thanks,

Lukas

[Portuga]

Try saving both documents in your C drive and do the tests from there.
If it works locally and not in the network (Shouldn´t really) you can limit the problem.