I have a long list of 'single point' numerical data items that a colleague
has extracted from a large written document. This data has been put into
rows; with column A containiung the data label/description, and column B
containing the actual data item (be it a general number, a date, a currency
amount, etc., etc..). I have used many of these elsewhere within the
workbook, but not all of them so some of the data is effectively redundant.

Is there an easy way of returning a "Yes" or "No" into column C, confirming
that the data point has been used elsewhere in the model (i.e. has
dependents)?

A macro button in the top left of the worksheet that when run does the
tests and then pastes either a "Yes" or "No" into column C, as appropriate,
for each row seems to be required, but I do not know what this would like.

Any help on how to write this would be very much appreciated.

Thanks

Mike

Mike,

there is probably a far more elegant solution, but the
following macro will test every entry in column B for
dependents, then fill in column C accordingly.

Sub DependOnIt()

Dim x As Integer

On Error Resume Next

For x = 1 To Application.WorksheetFunction.CountA _
(ActiveSheet.Columns(1))

If IsError(Cells(x, 2).Dependents) Then
Cells(x, 3).Value = "No"
Else
Cells(x, 3).Value = "Yes"
End If

Next x

On Error GoTo 0

End Sub

Cheers, Pete.
-----Original Message-----
I have a long list of 'single point' numerical data items
that a colleague
has extracted from a large written document. This data
has been put into
rows; with column A containiung the data
label/description, and column B
containing the actual data item (be it a general number,
a date, a currency
amount, etc., etc..). I have used many of these elsewhere
within the
workbook, but not all of them so some of the data is
effectively redundant.

Is there an easy way of returning a "Yes" or "No" into
column C, confirming
that the data point has been used elsewhere in the model
(i.e. has
dependents)?

A macro button in the top left of the worksheet that
when run does the
tests and then pastes either a "Yes" or "No" into column
C, as appropriate,
for each row seems to be required, but I do not know what
this would like.

Any help on how to write this would be very much
appreciated.

Thanks

Mike

Pete

Thanks for that - it works fine, but for the fact that if only looks for
dependents on the active sheet. Can it be easily extended to look for any
dependents across all sheets in the workbook?

Cheers

Mike



"Pete McCosh" wrote in message
...
Mike,

there is probably a far more elegant solution, but the
following macro will test every entry in column B for
dependents, then fill in column C accordingly.

Sub DependOnIt()

Dim x As Integer

On Error Resume Next

For x = 1 To Application.WorksheetFunction.CountA _
(ActiveSheet.Columns(1))

If IsError(Cells(x, 2).Dependents) Then
Cells(x, 3).Value = "No"
Else
Cells(x, 3).Value = "Yes"
End If

Next x

On Error GoTo 0

End Sub

Cheers, Pete.
-----Original Message-----
I have a long list of 'single point' numerical data items
that a colleague
has extracted from a large written document. This data
has been put into
rows; with column A containiung the data
label/description, and column B
containing the actual data item (be it a general number,
a date, a currency
amount, etc., etc..). I have used many of these elsewhere
within the
workbook, but not all of them so some of the data is
effectively redundant.

Is there an easy way of returning a "Yes" or "No" into
column C, confirming
that the data point has been used elsewhere in the model
(i.e. has
dependents)?

A macro button in the top left of the worksheet that
when run does the
tests and then pastes either a "Yes" or "No" into column
C, as appropriate,
for each row seems to be required, but I do not know what
this would like.

Any help on how to write this would be very much
appreciated.

Thanks

Mike

Mike,

Pete's solution is elegant and works on dependents in the same sheet. If you
want something that looks outside the sheet you can get a 30 day download of
my XspandXL add-in from my site below which has a lot of range tracing
utilities, including testing precedents/dependents in a range in external
sheets.

Robin Hammond
www.enhanceddatasystems.com


"Pete McCosh" wrote in message
...
Mike,

there is probably a far more elegant solution, but the
following macro will test every entry in column B for
dependents, then fill in column C accordingly.

Sub DependOnIt()

Dim x As Integer

On Error Resume Next

For x = 1 To Application.WorksheetFunction.CountA _
(ActiveSheet.Columns(1))

If IsError(Cells(x, 2).Dependents) Then
Cells(x, 3).Value = "No"
Else
Cells(x, 3).Value = "Yes"
End If

Next x

On Error GoTo 0

End Sub

Cheers, Pete.
-----Original Message-----
I have a long list of 'single point' numerical data items
that a colleague
has extracted from a large written document. This data
has been put into
rows; with column A containiung the data
label/description, and column B
containing the actual data item (be it a general number,
a date, a currency
amount, etc., etc..). I have used many of these elsewhere
within the
workbook, but not all of them so some of the data is
effectively redundant.

Is there an easy way of returning a "Yes" or "No" into
column C, confirming
that the data point has been used elsewhere in the model
(i.e. has
dependents)?

A macro button in the top left of the worksheet that
when run does the
tests and then pastes either a "Yes" or "No" into column
C, as appropriate,
for each row seems to be required, but I do not know what
this would like.

Any help on how to write this would be very much
appreciated.

Thanks

Mike

thanks for your help Pete

Cheers

Mike


"Pete McCosh" wrote in message
...
Mike,

there is probably a far more elegant solution, but the
following macro will test every entry in column B for
dependents, then fill in column C accordingly.

Sub DependOnIt()

Dim x As Integer

On Error Resume Next

For x = 1 To Application.WorksheetFunction.CountA _
(ActiveSheet.Columns(1))

If IsError(Cells(x, 2).Dependents) Then
Cells(x, 3).Value = "No"
Else
Cells(x, 3).Value = "Yes"
End If

Next x

On Error GoTo 0

End Sub

Cheers, Pete.
-----Original Message-----
I have a long list of 'single point' numerical data items
that a colleague
has extracted from a large written document. This data
has been put into
rows; with column A containiung the data
label/description, and column B
containing the actual data item (be it a general number,
a date, a currency
amount, etc., etc..). I have used many of these elsewhere
within the
workbook, but not all of them so some of the data is
effectively redundant.

Is there an easy way of returning a "Yes" or "No" into
column C, confirming
that the data point has been used elsewhere in the model
(i.e. has
dependents)?

A macro button in the top left of the worksheet that
when run does the
tests and then pastes either a "Yes" or "No" into column
C, as appropriate,
for each row seems to be required, but I do not know what
this would like.

Any help on how to write this would be very much
appreciated.

Thanks

Mike