Prev: Vlookup with If condition
Next: Auto Populate
From: sly411 on 19 Mar 2010 14:05
Cell A1 countsif a value appears in cells b1; b2 or b3. I want to copy cell
A1 into cell A2 etc but want the countsif reference to jump to b4; b5 or b6.
Currently if I copy it down to A2 the reference changes to b2; b3 and b5.

I need to copy hundreds of cells like this and I'm trying to avoid doing it
manually.
From: Luke M on 19 Mar 2010 14:40
Sometihng like this:

=COUNTIF(OFFSET($B$1,INT((ROW(A1)-1)*3),,3),"Find me")

--
Best Regards,

Luke M
"sly411" <sly411(a)discussions.microsoft.com> wrote in message
news:6A97E2F8-7EA8-444B-873C-872E0BCA0D74(a)microsoft.com...
> Cell A1 countsif a value appears in cells b1; b2 or b3. I want to copy
> cell
> A1 into cell A2 etc but want the countsif reference to jump to b4; b5 or
> b6.
> Currently if I copy it down to A2 the reference changes to b2; b3 and b5.
>
> I need to copy hundreds of cells like this and I'm trying to avoid doing
> it
> manually.


From: sly411 on 20 Mar 2010 15:04
Thanks. It worked perfectly but I don't understand why.

"Luke M" wrote:

> Sometihng like this:
>
> =COUNTIF(OFFSET($B$1,INT((ROW(A1)-1)*3),,3),"Find me")
>
> --
> Best Regards,
>
> Luke M
> "sly411" <sly411(a)discussions.microsoft.com> wrote in message
> news:6A97E2F8-7EA8-444B-873C-872E0BCA0D74(a)microsoft.com...
> > Cell A1 countsif a value appears in cells b1; b2 or b3. I want to copy
> > cell
> > A1 into cell A2 etc but want the countsif reference to jump to b4; b5 or
> > b6.
> > Currently if I copy it down to A2 the reference changes to b2; b3 and b5.
> >
> > I need to copy hundreds of cells like this and I'm trying to avoid doing
> > it
> > manually.
>
>
> .
>
From: sly411 on 20 Mar 2010 16:31
Thanks. Works great. A follow on question - how do I use the same capability
in Conditional formatting in the Applies To field. I have the following
formula to check for a condition:

=IF(CB3="Reg",MOD(CA3-BZ3,1)>TIME(0,$AG$39,0),IF(CB4="Reg",MOD(CA4-BZ4,1)>TIME(0,$AG$39,0),IF(CB5="Reg",MOD(CA5-BZ5,1)>TIME(0,$AG$39,0))))

Essentialy what it does is check if a value (Reg) is present in one of 3
sequential rows and if so calculates if the time difference between two
related values in the same row are greater than a predetermined number. If
TRUE then the cell is formatted. The formula works fine for a single cell
but when I use the Applies To field in CF and highlight the column I want to
apply it to, it increments the rows one at a time instead of jumping is
groups of 3. Can I somehow use Offset in the CF Applies To field? Thanks.

"Luke M" wrote:

> Sometihng like this:
>
> =COUNTIF(OFFSET($B$1,INT((ROW(A1)-1)*3),,3),"Find me")
>
> --
> Best Regards,
>
> Luke M
> "sly411" <sly411(a)discussions.microsoft.com> wrote in message
> news:6A97E2F8-7EA8-444B-873C-872E0BCA0D74(a)microsoft.com...
> > Cell A1 countsif a value appears in cells b1; b2 or b3. I want to copy
> > cell
> > A1 into cell A2 etc but want the countsif reference to jump to b4; b5 or
> > b6.
> > Currently if I copy it down to A2 the reference changes to b2; b3 and b5.
> >
> > I need to copy hundreds of cells like this and I'm trying to avoid doing
> > it
> > manually.
>
>
> .
>
 | 
Pages: 1
Prev: Vlookup with If condition
Next: Auto Populate