Filling in a column with text

[mmel]

I tried doing some searches on my problem, but it is so hard to
summarize in a few keywords. The subject, above, is not very accurately
descriptive, either.
Here is my issue:
I am an engineer in a production environment. We track the Downtime for
the various pieces of equipment that we run. We have a spreadsheet
where the production operators input the equipment data.
Each day has a time column depicting the 24 hours in a day in 15 minute
increments (15min/cell). To the right of the Time coIumn, I have a
column that a production operator is supposed to update every 15
minutes with the current state of the piece of equipment that they are
operating. The operator chooses from a drop down list of Downtime
categories that was created using Data Validation. The sheet has a
default state for the Downtime Categories column.
It is much more conducive to production to have the operators update
the state of the equipment only when an event occurs, not actually
every 15 minutes. So they don't have to keep coming back to the
computer every 15 minutes to update the state of the equipment after
there has been an event and because I don't want them to have to
backfill in the cells between events, I would like to be able to
automatically fill in all the cells below the event with the current
Equipment State category.
Example: The equipment has been running fine for the first 2 hours of a
shift (6-8am). The operator, at the start of the shift, had changed the
Default entry to 'Up'. I would like all the cells below that entry to
change from the Default to 'Up'. Let's say then, after 2 hours the
equipment breaks and needs repairs. The operator would then change the
entry at 8am to 'Equipment Failure'. I would like all the cells, below
8am, to then read 'Equipment Failure'.
How do I do this???????

 

[Guest]

Perhaps a different approach:

Instead of having machine operators scanning down a list of machine_ID's and
across a row of times to find an intersection, maybe this?:

Start with these column headings
A1: Machine_ID
B1: Event Time
C1: Status

Under A1 are data validation cells with a list of all machine_ID's
Col_B is formatted for time
Under C1 are data validation cells with a list of all statuses

Have the operator start each shift by selecting each machine_ID from the
dropdown list, entering the time, and entering the machine status.

Then....when an event occurs....select the machine_ID in the next cell in
the list, mark the time, and select the event.

Now for the status report:
H1: Machine ID
Col_I is blank
J1A1 (enter times in 15 min increments beginning with 0:15 AM)

H2: (first machine ID)
J2:
=IF(ISNA(MATCH(ROUND(J$1,5),IF(($A$2:$A$10=$H2),ROUND($B$2:$B$10,5),$A$2:$A$10),0)),I2,INDEX($C$2:$C$10,MATCH(ROUND(J$1,5),IF(($A$2:$A$10=$H2),ROUND($B$2:$B$10,5),$A$2:$A$10),0),1))

Note_1: I2 is an array formula and must be committed with
[ctrl][shift][enter], instead of just [enter].
Note_2: Since text wrap will impact the display, there are NO spaces in that
formula.

Copy J2 into K2 and across to DA2
Copy J2A2 and paste into J3A3 and down as far as needed.

Those formulas will display the initial status of each machine until the
status changes. The new status will display across the cells until another
change.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Hi

To do what you're talking about, you could simply save the following macro
into the relevant worksheet (e.g. sheet1 under Mircosoft Excel Objects under
VBAproject). Change the variables according to the range where your updates
are (in my case B3:B22, so MyCol = 2 (for B), MyRowStart = 3 and MyRowEnd =
22).

Private Sub Worksheet_Change(ByVal Target As Range)

MyCol = 2
MyRowStart = 3
MyRowEnd = 22

If Selection.Column <> MyCol Then
Exit Sub
End If
If Selection.Row < MyRowStart Or Selection.Row > MyRowEnd Then
Exit Sub
End If
Range(Cells(Selection.Row, MyCol), Cells(MyRowEnd, MyCol)) = Selection

End Sub

That should work. As always please save before running, as you can't undo
macros.

Regards
Glenton

 

[mmel]

Ron,
This approach would work if this was only a status board, but we also
extract data from this sheet to compute equipment downtime for the day
for each downtime category. So, it is a real-time tool and an
historical tool.

Perhaps a different approach:

Instead of having machine operators scanning down a list of machine_ID's and
across a row of times to find an intersection, maybe this?:

Start with these column headings
A1: Machine_ID
B1: Event Time
C1: Status

Under A1 are data validation cells with a list of all machine_ID's
Col_B is formatted for time
Under C1 are data validation cells with a list of all statuses

Have the operator start each shift by selecting each machine_ID from the
dropdown list, entering the time, and entering the machine status.

Then....when an event occurs....select the machine_ID in the next cell in
the list, mark the time, and select the event.

Now for the status report:
H1: Machine ID
Col_I is blank
J1A1 (enter times in 15 min increments beginning with 0:15 AM)

H2: (first machine ID)
J2:
=IF(ISNA(MATCH(ROUND(J$1,5),IF(($A$2:$A$10=$H2),ROUND($B$2:$B$10,5),$A$2:$A$10),0)),I2,INDEX($C$2:$C$10,MATCH(ROUND(J$1,5),IF(($A$2:$A$10=$H2),ROUND($B$2:$B$10,5),$A$2:$A$10),0),1))

Note_1: I2 is an array formula and must be committed with
[ctrl][shift][enter], instead of just [enter].
Note_2: Since text wrap will impact the display, there are NO spaces in that
formula.

Copy J2 into K2 and across to DA2
Copy J2A2 and paste into J3A3 and down as far as needed.

Those formulas will display the initial status of each machine until the
status changes. The new status will display across the cells until another
change.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

I tried doing some searches on my problem, but it is so hard to
summarize in a few keywords. The subject, above, is not very accurately
descriptive, either.
Here is my issue:
I am an engineer in a production environment. We track the Downtime for
the various pieces of equipment that we run. We have a spreadsheet
where the production operators input the equipment data.
Each day has a time column depicting the 24 hours in a day in 15 minute
increments (15min/cell). To the right of the Time coIumn, I have a
column that a production operator is supposed to update every 15
minutes with the current state of the piece of equipment that they are
operating. The operator chooses from a drop down list of Downtime
categories that was created using Data Validation. The sheet has a
default state for the Downtime Categories column.
It is much more conducive to production to have the operators update
the state of the equipment only when an event occurs, not actually
every 15 minutes. So they don't have to keep coming back to the
computer every 15 minutes to update the state of the equipment after
there has been an event and because I don't want them to have to
backfill in the cells between events, I would like to be able to
automatically fill in all the cells below the event with the current
Equipment State category.
Example: The equipment has been running fine for the first 2 hours of a
shift (6-8am). The operator, at the start of the shift, had changed the
Default entry to 'Up'. I would like all the cells below that entry to
change from the Default to 'Up'. Let's say then, after 2 hours the
equipment breaks and needs repairs. The operator would then change the
entry at 8am to 'Equipment Failure'. I would like all the cells, below
8am, to then read 'Equipment Failure'.
How do I do this???????