Reposition screen based on cell Content.

I just started getting into excel mostly to make my job a bit easier. I am would concider myself a novice. I also know a lot of programming, but am not familiar with Excels programming.

Anyways to the question.

I have a table that is set up with a column to enter an item and then the rest of the columns are used to mark which department that item belongs to. There are a few problems I have with this.

One is the fact that there are so many departments that the user has to scroll sideways if the item belongs to a later department.

The second is that the only way to tell department is by a code at the beginning of the product number. There are about 300 different codes corresponding to 5 departments.

My question is this: Is there any way at all to make it so when the user scans in a product number it will look at the first three letters and automatically scroll over to the right department.

It would be awesome if the user could also change which codes correspond to which department, so storing the values in an editable table maybe. But I think that is something I could figure out if I know how to do the first part.

Thank you in advance for your help.
Tom

In some out-of-the-way place, create a two-column range with three-letter codes down the left column and column numbers down the right column.

Sort the range in ascending order by the left column.

Select the range, and in the Names box (left of the formula bar), type lutDepCol

Assuming the product codes are in column A, select column B and do Window > Freeze Panes.

Right click on the sheet tab, select View Code from the context menu, and paste this in the window that opens:

Please Login or Register  to view this content.

Ok, so I have tried that and I understand what you are trying to make me do. However I cant get it to work. I am not too sure what the code is actually doing..

I have attached a dummy file I have made up this is laid out very similar to how I want it to be in the end but I did not include stuff that is irrelevant to the current problem.

I also changed the name of the range because I do not no how to Un name the ranges and now that name refers to a different range..

Thank you for your help. I will play around with this a bit more trying to figure it out.


Tom

Never mind. I figured it out. I was a bit confused by the column numbers and I had entered the column letters. Replaced it to the numbers and it works like a charm.

Thank you very much
Tom

You're welcome, glad it worked for you

Ok one more question. I got it working but what is there any to get rid of the error message that pops up if someone enters a number not in my range?


Thanks,
Tom

Please Login or Register  to view this content.

I am using that code so that one table refers to another table to change the position. The problem is if someone entered an invalid number say 2343245 instead of XYZ3245 i get a runtime error 1004.

I dont want this to happen every time if someone makes a mistake I just want it to do nothing. Thanks.

Tom