Is there a "Find If" type command?
 

Hi,

I am trying to create a workbook for scheduling different jobs on our 10
different machines. I have a different worksheet for each machine, with a
separate "input" tab for the jobs to initially be entered on.

The problem I have is in highlighting a job if it has not been scheduled. Is
there any way that anyone knows that I can highlight a job, or have a result
come up, on the Input tab, if that job does not appear in one of the machine
worksheets?

For info there are about 50 different jobs each week, so over a month one
will be scheduling 200+ jobs.

Thanks for any help.

Will

Is there a "Find If" type command?
 

making some assumptions:
1. that on your input sheet you are keeping a list of all the jobs
2. that on your machine tabs you are keeping a list of all job
allocated to that machine

then on the input sheet you can maintain 10 columns one for eac
machine with an indicator to show whether it appears on the machin
tab. You have a number of option as to how to create the value but th
MATCH function seems most likely. As in =NOT(ISERROR(MATCH(B10,'Machin
1'!B:B,0))) this will return TRUE if the value in cell B10 is on ta
Machine 1 in column B

You can then maintain an eleventh column to indicate that it appears o
any one of the sheets eg =or(d3=true,e3=true,f3=true )

of course you can create this all as one function.

regards

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=50166

Is there a "Find If" type command?
 

You could search the input tab list using code similar to:

Sub FindAcrossMultipleSheets()
Dim findStr As String
Dim wkSht As Worksheet
Dim found As Range
Dim rng as Range, cell as Range

With worksheets("Inputs")
set rng = .Range(.cells(2,1),.cells(rows.count,1).End(xlup))
End With
for each cell in rng
findStr = cell.Value
For Each wkSht In Sheets
if wkSht.Name < "Inputs" then
With wkSht

Set found = .Cells.Find(What:=findStr, LookIn:=xlValues, _
Lookat:=xlWhole, MatchCase:=False)


If Not found Is Nothing Then
' write name of machine and date to start
cell.Cells(cell.row,"G").Value = wksht.name
cell.Cells(cell.row,"H").Value = found.Cells(found.row,"C").Value
End If
End With
Next wkSht
Next cell

End Sub


Adjust to fit your actual situation. Code is untested and may contain
typos.


--
Regards,
Tom Ogilvy



"will" wrote in message
...
Hi,

I am trying to create a workbook for scheduling different jobs on our 10
different machines. I have a different worksheet for each machine, with a
separate "input" tab for the jobs to initially be entered on.

The problem I have is in highlighting a job if it has not been scheduled.
Is
there any way that anyone knows that I can highlight a job, or have a
result
come up, on the Input tab, if that job does not appear in one of the
machine
worksheets?

For info there are about 50 different jobs each week, so over a month one
will be scheduling 200+ jobs.

Thanks for any help.

Will