I am preparing an ACCESS table using apend query from a excel worksheet. I
identify deleted data in excel worksheet by striking through it rather than
completely deleting it.

But i do not want this striked through data to come to access table. is
there a way in which i can automatically do this? or can i set up excel to
return me any specific character in one cell to indicate that that row is
deleted. so i can inturn use that in access.

Thanks

Hi!

Here's a possibility....

Assume you have a data range from A1:A10 and some cells have been formatted
with a stike through.

Create this named formula:

Goto InsertNameDefine
Name: IsStike
Refers to: =GET.CELL(23,INDIRECT("RC[-1]",FALSE)

In a helper coulumn, column B, enter this formula and copy down to B10:

=IsStrike

This will return either TRUE or FALSE

The Indirect function is using R1C1 reference style. In the above example
the reference is to the cell immediately to the left of the cell containing
the formula. If the data is in column A and the formula, =IsStrike, were in
column F then the reference would be:

=GET.CELL(23,INDIRECT("RC[-5]",FALSE)

Which basically means offset the cell that contains the formula by zero rows
and 5 columns to the left.

A VBA UDF would be much better but someone that can write one would need to
respond. In the meantime, this will work.

Biff

"Jaldhi Shukla" <Jaldhi wrote in message
...
I am preparing an ACCESS table using apend query from a excel worksheet. I
identify deleted data in excel worksheet by striking through it rather
than
completely deleting it.

But i do not want this striked through data to come to access table. is
there a way in which i can automatically do this? or can i set up excel to
return me any specific character in one cell to indicate that that row is
deleted. so i can inturn use that in access.

Thanks

Note: that GET.CELL method is testing the cell FORMAT. If you had a cell
formatted with strike through the =IsStrike formula will return TRUE even if
there was no value in the cell because it's testing the FORMAT and not the
contents.

Biff

"Biff" wrote in message
...
Hi!

Here's a possibility....

Assume you have a data range from A1:A10 and some cells have been
formatted with a stike through.

Create this named formula:

Goto InsertNameDefine
Name: IsStike
Refers to: =GET.CELL(23,INDIRECT("RC[-1]",FALSE)

In a helper coulumn, column B, enter this formula and copy down to B10:

=IsStrike

This will return either TRUE or FALSE

The Indirect function is using R1C1 reference style. In the above example
the reference is to the cell immediately to the left of the cell
containing the formula. If the data is in column A and the formula,
=IsStrike, were in column F then the reference would be:

=GET.CELL(23,INDIRECT("RC[-5]",FALSE)

Which basically means offset the cell that contains the formula by zero
rows and 5 columns to the left.

A VBA UDF would be much better but someone that can write one would need
to respond. In the meantime, this will work.

Biff

"Jaldhi Shukla" <Jaldhi wrote in message
...
I am preparing an ACCESS table using apend query from a excel worksheet. I
identify deleted data in excel worksheet by striking through it rather
than
completely deleting it.

But i do not want this striked through data to come to access table. is
there a way in which i can automatically do this? or can i set up excel
to
return me any specific character in one cell to indicate that that row is
deleted. so i can inturn use that in access.

Thanks