how to design spreadsheets to collect data and display the totalmaterial used in a plating departmen

[Dan Dungan]

I'm posting here because I think may plan will need vba. I've tried
developing in Access, but the boss wants excel. I've looked for code
to use for looping through spreadsheets, but I haven't found an idea
yet. I don't know how to write the code for my plan.
-----------------------------------------------------------------------------------------------------------
Details:
We're using Excel 2003 on windows xp.

The boss wants to use excel to summarize material use in our plating
department.

We have several plating codes. Each plating code represents a process
the uses several tanks of differing solutions. Each tank has a code.

The same tank may be used in several processes.

We will determine the weight for each job and assign a load number.

We want to get a total weight of material that passed through each
tank and receive a notice when the total reaches 200 pounds.

The boss wants the administrator to enter the weight for each load
daily. There may be more than one load per day. Then he wants the
spreadsheet to add that weight to each of the tanks used in that
specific plating code.

Then he wants a summary that shows the total weight for all tanks.

There are several tanks, I haven't been able to determine the total
quantity of tanks yet. Here's a partial list of Tank names:

A20,A17,A18,A19,B11,B12,B13,B14,B19,B21,B22,B23,B24,B25,B27,B28,B29,B30,B31,B32,B33,B34,B35,B36,B37,B38,B39,B40,B42,B43,B44,B45,B46,B47,B48,B49,B50,B51,B52,B53,B54,B55,B60,B61,B62,B63,B65,B66,B67,C1,C10,C11,C12,C13,C16,C17,C19,C2,C20,C21,C27,C3,C4,C5,C6,C7,C8,C9

Here's a partial list of plating codes:

10,10A,24,25,30,30A,30B,48,87,90,99

Here's a partial list of tanks used in plating codes:

10A
B35 B36 B37 B38 B39 B40 B21 C10

24
B27 B28 B29 B30 B31 B32 B33 B34 B44 B46 B45 B47 B48 B49 C16 B50 C17
B51 C19 B52 C20 B53 C21 B54 B55 C11 C12 C13 C10 B19

30
B35 B36 B37 B60 B61 B62 B63
----------------------------------------------------------------------------------------------------------

Proposed plan:

So what feedback do you have for the following plan?

Each spreadsheet in the workbook represents activity for one plating
code.

Populate the column headers with the tank names used in that plating
code's process.

Include a column for date and a column for load number.

The administrator enters the date on the next row in cell A, then
enters the load number in cell B, then the weight in Cell C. The
spreadsheet fills that amount for all the tanks in that process.

After all the jobs are entered for the day. The administrator runs a
macros that loops through the workbook to determine the total for each
tank and put that amount another sheet--the summary sheet which will
have every tank listed in the header column.

 

[Vacuum Sealed]

I'm posting here because I think may plan will need vba. I've tried
developing in Access, but the boss wants excel. I've looked for code
to use for looping through spreadsheets, but I haven't found an idea
yet. I don't know how to write the code for my plan.
-----------------------------------------------------------------------------------------------------------
Details:
We're using Excel 2003 on windows xp.

The boss wants to use excel to summarize material use in our plating
department.

We have several plating codes. Each plating code represents a process
the uses several tanks of differing solutions. Each tank has a code.

The same tank may be used in several processes.

We will determine the weight for each job and assign a load number.

We want to get a total weight of material that passed through each
tank and receive a notice when the total reaches 200 pounds.

The boss wants the administrator to enter the weight for each load
daily. There may be more than one load per day. Then he wants the
spreadsheet to add that weight to each of the tanks used in that
specific plating code.

Then he wants a summary that shows the total weight for all tanks.

There are several tanks, I haven't been able to determine the total
quantity of tanks yet. Here's a partial list of Tank names:

A20,A17,A18,A19,B11,B12,B13,B14,B19,B21,B22,B23,B24,B25,B27,B28,B29,B30,B31,B32,B33,B34,B35,B36,B37,B38,B39,B40,B42,B43,B44,B45,B46,B47,B48,B49,B50,B51,B52,B53,B54,B55,B60,B61,B62,B63,B65,B66,B67,C1,C10,C11,C12,C13,C16,C17,C19,C2,C20,C21,C27,C3,C4,C5,C6,C7,C8,C9

Here's a partial list of plating codes:

10,10A,24,25,30,30A,30B,48,87,90,99

Here's a partial list of tanks used in plating codes:

10A
B35 B36 B37 B38 B39 B40 B21 C10

24
B27 B28 B29 B30 B31 B32 B33 B34 B44 B46 B45 B47 B48 B49 C16 B50 C17
B51 C19 B52 C20 B53 C21 B54 B55 C11 C12 C13 C10 B19

30
B35 B36 B37 B60 B61 B62 B63
----------------------------------------------------------------------------------------------------------

Proposed plan:

So what feedback do you have for the following plan?

Each spreadsheet in the workbook represents activity for one plating
code.

Populate the column headers with the tank names used in that plating
code's process.

Include a column for date and a column for load number.

The administrator enters the date on the next row in cell A, then
enters the load number in cell B, then the weight in Cell C. The
spreadsheet fills that amount for all the tanks in that process.

After all the jobs are entered for the day. The administrator runs a
macros that loops through the workbook to determine the total for each
tank and put that amount another sheet--the summary sheet which will
have every tank listed in the header column.

Hi

I shouldn't imagine you would require a workbook for each Job Code, when
it could be handle in one using a few worksheets. This makes more sense
as it doesn't require external linking.

You could Name the workbook 2011-2012 depending on how you structure
your financial year, then use 12 sheets to represent each month of the
year, then input the data directly for each month, using summary sheets
and graphs to display any trends.

Each job's material composition could be stored in a Table Array broken
down to a 1 pound amount so that it can be multiplied by whatever weight
is being processed, the actual Job Code could be stored, and referenced
Via Data Validation Dropdown; Or use the OnChange(Target/Intersect) to
trigger the values to be inserted from the Table into the appropriate cells.

considerations:

My brief exposure the Plating, ie Chrome, way back in 1978 [when I was
young, dumb, indestructible & had a massive head of hair...lol], if I
recall the major consumed component of it was Nickel Chips and Chromium
Solution, that said there were other processes for cleaning prior to
that process that escapes me.

Moving on, how does your boss intend to deal with any waste from
imperfect finishing, by that I mean will you be applying a -/+ % factor
to allow for this issue, it may not be an obvious short-term
consideration, but over time ( re-processing imperfects ) and using your
intended structure/purpose for the workbook would mean the formula for
maintaining a pseudo "Just-In-Time" process of materials ordering would
lead to a shortfall down the road.

I am currently on holidays and have some time spare in which I could put
some thought into a workbook. I will let you know when I have something
worth conveying to you.

HTH
Mick.

 

[Vacuum Sealed]

I'm posting here because I think may plan will need vba. I've tried
developing in Access, but the boss wants excel. I've looked for code
to use for looping through spreadsheets, but I haven't found an idea
yet. I don't know how to write the code for my plan.
-----------------------------------------------------------------------------------------------------------

Details:
We're using Excel 2003 on windows xp.

The boss wants to use excel to summarize material use in our plating
department.

We have several plating codes. Each plating code represents a process
the uses several tanks of differing solutions. Each tank has a code.

The same tank may be used in several processes.

We will determine the weight for each job and assign a load number.

We want to get a total weight of material that passed through each
tank and receive a notice when the total reaches 200 pounds.

The boss wants the administrator to enter the weight for each load
daily. There may be more than one load per day. Then he wants the
spreadsheet to add that weight to each of the tanks used in that
specific plating code.

Then he wants a summary that shows the total weight for all tanks.

There are several tanks, I haven't been able to determine the total
quantity of tanks yet. Here's a partial list of Tank names:

A20,A17,A18,A19,B11,B12,B13,B14,B19,B21,B22,B23,B24,B25,B27,B28,B29,B30,B31,B32,B33,B34,B35,B36,B37,B38,B39,B40,B42,B43,B44,B45,B46,B47,B48,B49,B50,B51,B52,B53,B54,B55,B60,B61,B62,B63,B65,B66,B67,C1,C10,C11,C12,C13,C16,C17,C19,C2,C20,C21,C27,C3,C4,C5,C6,C7,C8,C9


Here's a partial list of plating codes:

10,10A,24,25,30,30A,30B,48,87,90,99

Here's a partial list of tanks used in plating codes:

10A
B35 B36 B37 B38 B39 B40 B21 C10

24
B27 B28 B29 B30 B31 B32 B33 B34 B44 B46 B45 B47 B48 B49 C16 B50 C17
B51 C19 B52 C20 B53 C21 B54 B55 C11 C12 C13 C10 B19

30
B35 B36 B37 B60 B61 B62 B63
----------------------------------------------------------------------------------------------------------


Proposed plan:

So what feedback do you have for the following plan?

Each spreadsheet in the workbook represents activity for one plating
code.

Populate the column headers with the tank names used in that plating
code's process.

Include a column for date and a column for load number.

The administrator enters the date on the next row in cell A, then
enters the load number in cell B, then the weight in Cell C. The
spreadsheet fills that amount for all the tanks in that process.

After all the jobs are entered for the day. The administrator runs a
macros that loops through the workbook to determine the total for each
tank and put that amount another sheet--the summary sheet which will
have every tank listed in the header column.

Hi

I shouldn't imagine you would require a workbook for each Job Code, when
it could be handle in one using a few worksheets. This makes more sense
as it doesn't require external linking.

You could Name the workbook 2011-2012 depending on how you structure
your financial year, then use 12 sheets to represent each month of the
year, then input the data directly for each month, using summary sheets
and graphs to display any trends.

Each job's material composition could be stored in a Table Array broken
down to a 1 pound amount so that it can be multiplied by whatever weight
is being processed, the actual Job Code could be stored, and referenced
Via Data Validation Dropdown; Or use the OnChange(Target/Intersect) to
trigger the values to be inserted from the Table into the appropriate
cells.

considerations:

My brief exposure the Plating, ie Chrome, way back in 1978 [when I was
young, dumb, indestructible & had a massive head of hair...lol], if I
recall the major consumed component of it was Nickel Chips and Chromium
Solution, that said there were other processes for cleaning prior to
that process that escapes me.

Moving on, how does your boss intend to deal with any waste from
imperfect finishing, by that I mean will you be applying a -/+ % factor
to allow for this issue, it may not be an obvious short-term
consideration, but over time ( re-processing imperfects ) and using your
intended structure/purpose for the workbook would mean the formula for
maintaining a pseudo "Just-In-Time" process of materials ordering would
lead to a shortfall down the road.

I am currently on holidays and have some time spare in which I could put
some thought into a workbook. I will let you know when I have something
worth conveying to you.

HTH
Mick.

One other question

The "Partial List" of Tank Names, your sample A20 etc, is this the
actual name or is the the Cell reference to the actual tanks name..???

Mick.

 

[GS]

Uh.., I doubt you're going to get this resolved through replies to your
post because this project requires some rather advanced design
techniques<IMO>! I'm sure you get lots of suggestions but if you don't
have the necessary skills level to put it together you're in for an
enduring ride!

Mick's suggestion for using 1 file per fiscal year and 1 sheet per
month is a good place to start. I wouldn't go with daily inputs that
get consolidated on monthly sheets, though. Each month sheet can
receive daily input AND collect totals for each tank. What's critical,
IMO, is how the tank loads are calc'd. I'd use worksheet formulas to do
this based on lookups for each plating code's tank usage. Fairly simple
concept but will require some complex structuring for it to work
efficiently and reliably. Implementing this will require an extra sheet
to contain the plating codes and their respective tank lists.

I'd probably add an extra sheet to consolidate month totals so the
entire fiscal year can be viewed in one place. This also can be
accomplished via worksheet formulas. (The reason I suggest using
worksheet formulas is because they give instant results and process
much more efficiently that VBA could duplicate)

So.., at the very least I'd go with a 14 sheet reusable template that
makes extensive use of defined name ranges for use in the formulas, and
dynamic ranges for the plating code lists so you can easily edit them
without worrying about screwing up the design structure.

LOL

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Auric__]

I'm posting here because I think may plan will need vba. I've tried
developing in Access, but the boss wants excel.

Why? IMO, your requirements below are more suited to a database than a
series of spreadsheets.

I've looked for code
to use for looping through spreadsheets, but I haven't found an idea
yet. I don't know how to write the code for my plan.
-------------------------------------------------------------------------
----------------------------------
Details:
We're using Excel 2003 on windows xp.

The boss wants to use excel to summarize material use in our plating
department.

We have several plating codes. Each plating code represents a process
the uses several tanks of differing solutions. Each tank has a code.

The same tank may be used in several processes.

We will determine the weight for each job and assign a load number.

We want to get a total weight of material that passed through each
tank and receive a notice when the total reaches 200 pounds.

The boss wants the administrator to enter the weight for each load
daily. There may be more than one load per day. Then he wants the
spreadsheet to add that weight to each of the tanks used in that
specific plating code.

Then he wants a summary that shows the total weight for all tanks.

There are several tanks, I haven't been able to determine the total
quantity of tanks yet. Here's a partial list of Tank names:

A20,A17,A18,A19,B11,B12,B13,B14,B19,B21,B22,B23,B24,B25,B27,B28,B29,B30,B
31,B32,B33,B34,B35,B36,B37,B38,B39,B40,B42,B43,B44,B45,B46,B47,B48,B49,B5
0,B51,B52,B53,B54,B55,B60,B61,B62,B63,B65,B66,B67,C1,C10,C11,C12,C13,C16,
C17,C19,C2,C20,C21,C27,C3,C4,C5,C6,C7,C8,C9

Here's a partial list of plating codes:

10,10A,24,25,30,30A,30B,48,87,90,99

Here's a partial list of tanks used in plating codes:

10A
B35 B36 B37 B38 B39 B40 B21 C10

24
B27 B28 B29 B30 B31 B32 B33 B34 B44
B46 B45 B47 B48 B49 C16 B50 C17
B51 C19 B52 C20 B53 C21 B54 B55
C11 C12 C13 C10 B19

30
B35 B36 B37 B60 B61 B62 B63

Have you asked anyone? I bet that someone, somewhere, has complete lists of
tanks and plating codes.

Proposed plan:

So what feedback do you have for the following plan?

Each spreadsheet in the workbook represents activity for one plating
code.

Populate the column headers with the tank names used in that plating
code's process.

Include a column for date and a column for load number.

The administrator enters the date on the next row in cell A, then
enters the load number in cell B, then the weight in Cell C. The
spreadsheet fills that amount for all the tanks in that process.

After all the jobs are entered for the day. The administrator runs a
macros that loops through the workbook to determine the total for each
tank and put that amount another sheet--the summary sheet which will
have every tank listed in the header column.

If I were to do this, I would probably go with an Access backend (unless I
decided a more powerful db engine was needed, in which case I'd likely use
MySQL) and tell the boss to go f*** himself. Then I'd just pull the data
from the db as needed.

 

[rumkus]

I'm posting here because I think may plan will need vba. I've tried
developing in Access, but the boss wants excel. I've looked for code
to use for looping through spreadsheets, but I haven't found an idea
yet. I don't know how to write the code for my plan.
-----------------------------------------------------------------------------------------------------------
Details:
We're using Excel 2003 on windows xp.

The boss wants to use excel to summarize material use in our plating
department.

We have several plating codes. Each plating code represents a process
the uses several tanks of differing solutions. Each tank has a code.

The same tank may be used in several processes.

We will determine the weight for each job and assign a load number.

We want to get a total weight of material that passed through each
tank and receive a notice when the total reaches 200 pounds.

The boss wants the administrator to enter the weight for each load
daily. There may be more than one load per day. Then he wants the
spreadsheet to add that weight to each of the tanks used in that
specific plating code.

Then he wants a summary that shows the total weight for all tanks.

There are several tanks, I haven't been able to determine the total
quantity of tanks yet. Here's a partial list of Tank names:

A20,A17,A18,A19,B11,B12,B13,B14,B19,B21,B22,B23,B24,B25,B27,B28,B29,B30,B31,B32,B33,B34,B35,B36,B37,B38,B39,B40,B42,B43,B44,B45,B46,B47,B48,B49,B50,B51,B52,B53,B54,B55,B60,B61,B62,B63,B65,B66,B67,C1,C10,C11,C12,C13,C16,C17,C19,C2,C20,C21,C27,C3,C4,C5,C6,C7,C8,C9

Here's a partial list of plating codes:

10,10A,24,25,30,30A,30B,48,87,90,99

Here's a partial list of tanks used in plating codes:

10A
B35 B36 B37 B38 B39 B40 B21 C10

24
B27 B28 B29 B30 B31 B32 B33 B34 B44 B46 B45 B47 B48 B49 C16 B50 C17
B51 C19 B52 C20 B53 C21 B54 B55 C11 C12 C13 C10 B19

30
B35 B36 B37 B60 B61 B62 B63
----------------------------------------------------------------------------------------------------------

Proposed plan:

So what feedback do you have for the following plan?

Each spreadsheet in the workbook represents activity for one plating
code.

Populate the column headers with the tank names used in that plating
code's process.

Include a column for date and a column for load number.

The administrator enters the date on the next row in cell A, then
enters the load number in cell B, then the weight in Cell C. The
spreadsheet fills that amount for all the tanks in that process.

After all the jobs are entered for the day. The administrator runs a
macros that loops through the workbook to determine the total for each
tank and put that amount another sheet--the summary sheet which will
have every tank listed in the header column.

First some appropriate formulae here and there then I'd let the pivot tables finish the job.

 

[Vacuum Sealed]

Why? IMO, your requirements below are more suited to a database than a
series of spreadsheets.


Have you asked anyone? I bet that someone, somewhere, has complete lists of
tanks and plating codes.


If I were to do this, I would probably go with an Access backend (unless I
decided a more powerful db engine was needed, in which case I'd likely use
MySQL) and tell the boss to go f*** himself. Then I'd just pull the data
from the db as needed.

Auric, OP had stated he was not able to do it in Access and that his
boss wants it done in Excel.

Garry, I threw together a basic template for him with which he can have
a look at an alternative which, you must have been thinking along the
same lines as that was exactly what I emailed to Dan (OP), approx 14
sheets one for each month, a Support Sheet containing a sample Array and
some Defined Name ranges for a DV so the user can select the Plate Code
and the and the adjacent cells do the look ups for each of the sample
Tank columns, a Summary Sheet with a monthly summation for each Tank in
addition, a daily one although I did not pursue it in great details so
as not to include any VB at this early point in time. I've kept
everything as simple as possible.

Mick

 

[GS]

Vacuum Sealed submitted this idea :

Garry, I threw together a basic template for him with which he can have a
look at an alternative which, you must have been thinking along the same
lines as that was exactly what I emailed to Dan (OP), approx 14 sheets one
for each month, a Support Sheet containing a sample Array and some Defined
Name ranges for a DV so the user can select the Plate Code and the and the
adjacent cells do the look ups for each of the sample Tank columns, a Summary
Sheet with a monthly summation for each Tank in addition, a daily one
although I did not pursue it in great details so as not to include any VB at
this early point in time. I've kept everything as simple as possible.

Mick,
I've 'been there, done that' for 2 separate (competitors) plating
companies. I also implement the same concept for countless clients for
accounting analysis projects. Hence the 13 fiscal sheets and 1 list
sheet for storing lists used for DV dropdowns and cell formula lookups.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Dan Dungan]

Hi Garry,

Thanks for your input.

Before I posted, I was thinking all those formulas would slow the
spreadsheet down, but Mick's idea changed my mind.

Thanks again for your ideas.

Dan

 

[Dan Dungan]

Hi Auric

Thanks for your questions and suggestions.

Why? IMO, your requirements below are more suited to a database than a
series of spreadsheets.

According to the owner, I don't need to keep the history.

Have you asked anyone? I bet that someone, somewhere, has complete lists of
tanks and plating codes.

I finally got a meeting with the plating manager tomorrow.

I'm not in a position to tell the boss that right now.

Thanks,

Dan

 

[Dan Dungan]

Hi Rum,

First some appropriate formulae here and there then I'd let the pivot tables finish the job.

Thanks, I like the pivot tables.

Dan

 

[Dan Dungan]

Hi Mick,

I just found out I'll be meeting with the manager tomorrow, so I'll
better understand the requirements then.

I think the simple ideas in your spreadsheet are a good start.

Thanks,

Dan

 

[Dan Dungan]

Hi Mick,

Thanks for your response.

Your spreadsheet showed a simple way to manage this task.

I shouldn't imagine you would require a workbook for each Job Code, when
it could be handle in one using a few worksheets. This makes more sense
as it doesn't require external linking.

I must have miscommunicated here. There is no job code.

I want to keep just one sheet for the data entry. I don't see any
reason to enter the different months on different sheets.

Each job's material composition could be stored in a Table Array broken
down to a 1 pound amount so that it can be multiplied by whatever weight
is being processed, the actual Job Code could be stored, and referenced
Via Data Validation Dropdown; Or use the OnChange(Target/Intersect) to
trigger the values to be inserted from the Table into the appropriate cells.

I'm no chemist, but I understand from the owner the purpose of this
spreadsheet is to stabilize the consistency of the tank chemistry. We
are not tracking the material for materials ordering. We are going to
put a predetermined weight for each job to see how many pounds of
material we can plate before the quality diminishes. The spreadsheet
will have served its purpose once we know that number of pounds.We'll keep using it, but I don't need to store the history for more
than about three months. I don't need to break it down by month. I
need to know the total number of pounds that went through the tank
when plating quality decreased.

Dan

 

[Dan Dungan]

Those are tank names.