FUNCTION OR CODE ?

[tomjoe]

Can anyone give direction or guidance ?

I have a workbook with 2 sheets.
Sheet 1 is a overview (map) of the different storehous locations in a
factory. Each location have a unic number. It looks like this:

A B C D E
1 LOC A.1 A.2 A.3 A.4
2 MAT1 442 433 458 433
3 MAT2 485 0 442 0
4 DATE 5.2 4.1 12.2 20.1
5
6 LOC B.1 B.2 B.3 B4
7 MAT1 485 442 458 0
8 MAT2 0 433 0 0
9 DATE 6.1 18.1 15.2 16.1


LOC is short for Location number, MAT1 is short for Material number 1, MAT2
is Material number 2, DATE is the date when the last material arrived at the
location.

In sheet 2 I have listed up all the Materialnumbers in column A and when
goods arrive I put in the Location numbers where the goods is physically
placed in column B, C, D and so forth. It looks something like this:

A B C D E F
1 433 A.2 A.4 B.2
2 442 A.1 A.3 B.2
3 444
4 445


I have just put in the location numbers for two material numbers in sheet 2
in order to illustrate the situation. I have also limited the amount of
different material numbers on each location to two in sheet 1.

Now comes the challenging part ....

1.)
I want sheet 1 to automatically be updated with material numbers (MAT1 and
MAT2) from Column A in sheet 2 when I put in the location in sheet 2. In the
illustration above I have put in Location A.2, A.4 and B.2 in row 1 where
materialnumber 433 is loacated. Then I want sheet 1 to automatically be
updated with materialnumber 433 under Location A.2, A.4 and B.2 - as in the
illustration above. In B.2 we can see that materialnumber 433 is
automatically placed as MAT2 because MAT1 was occupied with materialnumber
442.

I assume that this is very difficult to manage without a VBA code ?

2.)
Futher I also would like the date for the last movement in the location
(change of cells in the rows with MAT1 or MAT2 in sheet 1) to be
automatically logged.
This for sure needs a code.

Unfortunately I am not able to figure out this with the use of the available
functions in Excel. I assume I will need a VBA code or at least a UDF in
order to solve this ?

Will anyone take the challenge ?

 

[JLatham]

I think I have a problem with this - and I'll try to explain it and perhaps
you can clear it up for me.

Let's say you make a new entry on Sheet2 for item 442 for lets say location
A.5 (assuming A.5 exists on Sheet1), how do I know whether to associate the
new entry with MAT1 or MAT2, or do we add an entry for both MAT1 and MAT2?
Your example shows an entry for 442 at both MAT1/A.1 and MAT2/A.3. So we
don't know whether to associate then new 442/A.5 entry with MAT1 or MAT2.

Remember, there's (almost) always HelpFrom @ JLathamsite.com

Also, confirm that your date format is made of dd.m, where dd = the day of
the month and m = month number, where Christmas would be 25.12, right?

Oh - and what I have in mind would be some VBA based on a change in cells in
any column except column A on Sheet2, when there is something on the same row
in column A.

 

[tomjoe]

Thanks JLatham for your quick respons.

First of all: I am open for suggestions to simplify the scenario
(restructure the sheets, make a third sheet if necessary as a "extra step"
between sheet 1 and 2 or whatever). The overall purpose is to avoid having to
manually write in Location numbers in sheet 2 (A.2, A.4, B.2 etc.) and then
turn to sheet 1 to manually put in the material numbers (433, 442 etc).

I confirm the date format should be dd.mm.yy (25.12.08 for Christmas this
year).

Your question related to conflict of MAT1, MAT2: Actually I would like to
have four fields for materialnumbers (MAT1, MAT2, MAT3 and MAT4) for each
unic location (LOC) in sheet1. The entry on sheet 2 must not overwrite any
occupied materialnumber cells in sheet 1, so the system must check first if
the entrance cell for MAT1 is empty in sheet 1. If it is not empty it must go
on to MAT2 and so forth on that spesific location. If no one of the four
entrance cells for materials in the spesific location is empty in sheet 1
(the location is full), the system must not allow the user to pic that
spesific location in sheet 2.
That is to say max 4 entrance with the same locaion number entered (use of
List box/scroll bar) in sheet 2.

The locations (LOC) A.1, A.2 etc is about 600 different fixed unic numbers,
but the about 300 materialnumbers in column A in sheet 2 is changing (some
materials is cancelled and new once are added).

Was this helpful ?

Also, what you have in mind regarding some VBA based on a change in cells in
any column except column A on Sheet2, when there is something on the same
row sounds exactly what I am thinking of.

Many thanks so far.


JLatham skrev:

 

[JLatham]

Try this workbook out. Rather than pasting a lot of long code here, you can
simply upload this file. All of the code in it is 'within' Sheet2, so you
can get the functions into your workbook simply by copying Sheet2 from this
workbook into your own.

Since this is a first-trial of things, I suggest that you make a copy of
your current workbook as a test bed for all of this.

As for redesigning your workbook layout, I'm really not in a position to do
that. If this all works for you, then what you have is fine. I do see what
I perceive as possible shortcomings:
#1 - the inability to associate a number (as 442, 458, etc) with a
particular MAT# entry in a group, but you've said just take a top-down, first
empty cell in column approach to that, and so I have done that.
#2 - The date in the DATE row is going to be the last date that an entry was
made into that column for that location. Again, if this is good enough, then
no further consideration needs to be give.

http://www.jlathamsite.com/uploads/for_TomJoe.xls

Hope this makes life a little simpler for you.

 

[tomjoe]

Thank you JLatham

I have tested the code extensively and it worked as expected.
I am grateful for your help.

In danger of asking to much:

1.
Can you put put into the code the possibility to delete the entrances of the
materialnumbers in sheet 1 when I delete the location number from sheet 2.
Then I can work exclusively from sheet 2.


2.
Actually I need the possibility to use two decimals in the location numbers
format (Ex: A.2.1, C.32.2 etc.). Can it be done?

I clearly see that I need to learn writing codes myself. Do you have some
good tip off how I should approach the subject (books, courses, seminars
etc.) ?

Again
Thank you.


JLatham skrev:

 

[tomjoe]

Anyone !

I need only to get this code adjusted to accept the form A.2.1 (two periods).
I will not use the form A.2 but A.2.1


What do I have to change in this code :

Private Function ValidateLocIDFormat(entryCell As Range) As Boolean
Const validAlphas = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const validNumerics = "0123456789"
Const sepChar = "."
Dim tmpString1 As String
Dim alphaPortion As String
Dim numericPortion As String
Dim LC As Integer ' loop counter
'make assumption that it is invalid!
ValidateLocIDFormat = False
'remove leading/trailing spaces and convert all alphas to UPPERCASE
tmpString1 = UCase(Trim(entryCell.Value))
If Len(tmpString1) = 0 Then
Exit Function
End If
If InStr(tmpString1, sepChar) < 2 Or InStr(tmpString1, sepChar) =
Len(tmpString1) Then
'either no "." in it, or "." is 1st or last character: Invalid
Exit Function
End If
alphaPortion = Left(tmpString1, InStr(tmpString1, sepChar) - 1)
For LC = 1 To Len(alphaPortion)
If InStr(validAlphas, Mid(alphaPortion, LC, 1)) = 0 Then
Exit Function ' invalid: has something other than ABC... in left portion
End If
Next
numericPortion = Right(tmpString1, Len(tmpString1) - InStr(tmpString1,
sepChar))
For LC = 1 To Len(numericPortion)
If InStr(validNumerics, Mid(numericPortion, LC, 1)) = 0 Then
Exit Function ' invalid: has something other than a number in right
portion
End If
Next



JLatham skrev: