Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

relative addressing problem

3 views
Skip to first unread message

David Wheeler

unread,
Nov 24, 2004, 11:37:58 PM11/24/04
to corel.wpoffice.quattropro10

Hi,

I have a macro that imports data into a new sheet in a workbook;
seperates the standards from the samples; calculates the difference
between the reported value of the sample and its known value; averages
the differences of all the stds and applies this offset to the samples.

Often one (or more :-( ) of the samples is obviously unusable and so
should be excluded from the average difference. When doing this
manually I would change the font of the rejected std to 'strikeout' and
manually edit the Ave formula. I want to try and automate this stage.

My idea is to put another block about 20 rows below the stds with
formulae like

@IF(@PROPERTY("CE27.Font.Strikeout")="Yes","reject",CE27)

and then use PureAve on this block. If I build this manually it works.
However if one enters this formula in one cell, then selects a block
and fill-down the next line reads

@IF(@PROPERTY("CE27.Font.Strikeout")="Yes","reject",CE28)

Also if a seperate the formula into two cells like
@PROPERTY("CE27.Font.Strikeout")| @IF(CD47="Yes","reject",CE27)

the expression in @PROPERTY still does not change. I've tried various
ways to enter these formula (BlockFill, BlockCopy, PutBlock, Let and
maybe more) but to no avail. I've tried relative addresses
@PROPERTY("[]C(0)R(-20).Font.Strikeout") but it just references the
cell 20 rows above where the formula was first written, no matter where
it is copied to. I guess that the problem is that the arguement
CE27.Font.Strikeout is in quotes but I can't see a way around this.

The next step is to try to write a macro which one runs after crossing
out the rejects but I'd prefer to use functions so it updates automatically.

Any ideas?

cheers,
David

lemoto

unread,
Nov 25, 2004, 3:38:51 PM11/25/04
to corel.wpoffice.quattropro10

David:

> My idea is to put another block about 20 rows below the stds with
> formulae like
> @IF(@PROPERTY("CE27.Font.Strikeout")="Yes","reject",CE27)
> and then use PureAve on this block. If I build this manually it works.
> However if one enters this formula in one cell, then selects a block
> and fill-down the next line reads
> @IF(@PROPERTY("CE27.Font.Strikeout")="Yes","reject",CE28)

Being within double quotes, the reference is literal and so will not
change when copied.

I see no easy way around this.
You could use a macro that runs from <first Row> through <last Row> using
the same number (=variable, =content of some Cell) to identify each target
Row and the Row number to be used in the formula.
--
Good wishes!
Roy Lewis
C_Tech volunteer
(UK)


Jeff B

unread,
Nov 25, 2004, 4:48:35 PM11/25/04
to corel.wpoffice.quattropro10

Try this
@IF(@PROPERTY("CE"&@STRING(@ROW,0)&".Font.Strikeout")="Yes","Reject",CE27)

around which QP will add ARRAY()

Copy down the page


"David Wheeler" <dwhe...@uow.edu.au> wrote in message
news:41a5558a$1_2@cnews...

David Wheeler

unread,
Nov 29, 2004, 1:03:03 AM11/29/04
to corel.wpoffice.quattropro10

Jeff B wrote:
> Try this
> @IF(@PROPERTY("CE"&@STRING(@ROW,0)&".Font.Strikeout")="Yes","Reject",CE27)
>
> around which QP will add ARRAY()
>
> Copy down the page

That works well.

Thanks

0 new messages