Any (clever) way to automate ROW:COL in copy/paste???
 

Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?

--
- Zilla
(Remove XSPAM)

Any (clever) way to automate ROW:COL in copy/paste???
 

Thanks, I thought about the duplication, but NOT about
the hiding using conditional formatting.

"Debra Dalgleish" wrote in message
...
In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:
Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Any (clever) way to automate ROW:COL in copy/paste???
 

In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:
Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Any (clever) way to automate ROW:COL in copy/paste???
 

In your example below, you say to enter the formula in D1. However,
in my case, I just want D3 and D5 to contain the calculated sum. I should
have said, D3=2 and D5=1 in my orginal example. I want D1, D2, and D4
to be blank. I WILL enter another condition in the IF statement to take
care of this.

My question is do I enter your formula in all D cells?

"Debra Dalgleish" wrote in message
...
In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:
Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Any (clever) way to automate ROW:COL in copy/paste???
 

My formula already has an IF function that takes care of that.

Zilla wrote:
In your example below, you say to enter the formula in D1. However,
in my case, I just want D3 and D5 to contain the calculated sum. I should
have said, D3=2 and D5=1 in my orginal example. I want D1, D2, and D4
to be blank. I WILL enter another condition in the IF statement to take
care of this.

My question is do I enter your formula in all D cells?

"Debra Dalgleish" wrote in message
...

In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:

Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Any (clever) way to automate ROW:COL in copy/paste???
 

Yes, I missed it. I did try it and it worked! Thanks again!

"Debra Dalgleish" wrote in message
...
My formula already has an IF function that takes care of that.

Zilla wrote:
In your example below, you say to enter the formula in D1. However,
in my case, I just want D3 and D5 to contain the calculated sum. I
should
have said, D3=2 and D5=1 in my orginal example. I want D1, D2, and D4
to be blank. I WILL enter another condition in the IF statement to take
care of this.

My question is do I enter your formula in all D cells?

"Debra Dalgleish" wrote in message
...

In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:

Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Any (clever) way to automate ROW:COL in copy/paste???
 

Hi Deb

Provided the OP uses your suggestion of inserting the values in column A
and hiding some with CF, then a slightly simplified and non-volatile
solution would be to enter in D1

=IF(A2=A1,"",SUMPRODUCT(($A$1:$A$100=A1)*($C$1:$C$ 100="Y")*$B$1:$B$100))
and copy down

Change the length of the ranges to suit the volume of data.
--
Regards

Roger Govier


"Debra Dalgleish" wrote in message
...
My formula already has an IF function that takes care of that.

Zilla wrote:
In your example below, you say to enter the formula in D1. However,
in my case, I just want D3 and D5 to contain the calculated sum. I
should
have said, D3=2 and D5=1 in my orginal example. I want D1, D2, and D4
to be blank. I WILL enter another condition in the IF statement to
take
care of this.

My question is do I enter your formula in all D cells?

"Debra Dalgleish" wrote in message
...

In column A, enter the number in each row. You can hide the
duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:

Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Any (clever) way to automate ROW:COL in copy/paste???
 

You're welcome! Thanks for letting me know that it worked.

Zilla wrote:
Yes, I missed it. I did try it and it worked! Thanks again!

"Debra Dalgleish" wrote in message
...

My formula already has an IF function that takes care of that.

Zilla wrote:

In your example below, you say to enter the formula in D1. However,
in my case, I just want D3 and D5 to contain the calculated sum. I

should

have said, D3=2 and D5=1 in my orginal example. I want D1, D2, and D4
to be blank. I WILL enter another condition in the IF statement to take
care of this.

My question is do I enter your formula in all D cells?

"Debra Dalgleish" wrote in message
...


In column A, enter the number in each row. You can hide the duplicates
with conditional formatting:

http://www.contextures.com/xlCondFor...html#Duplicate

Then, in D1, use the following formula:

=IF(A1=A2,"",SUMIF(OFFSET(C1,-(COUNTIF(A:A,A1)-1),0):C1,
"Y",OFFSET(B1,-(COUNTIF(A:A,A1)-1),0):B1))

Zilla wrote:


Say I have the following data; in D, I want to sum values
in B if corresponding values in C=Y. The value in A serves
to visually "group" the data in B,C & D; meaning, data samples
"1" (A1) uses B1-D3, data samples 2 (A4) uses B4-D5. Simple
enough...

A B C D
1 1 1 Y
2 1 N
3 1 Y sumif($C1:$C3, "=Y", $B1:$B3), D=2
(Copy and paste 3 rows above, but only use 2 rows
for next data)
4 2 1 N
5 1 Y sumif($C3:$C5, "=Y", $B3:$B5); D=1

The problem is, as illustrated, the copied/pasted formula
on D5 starts in C3, since the orig. formula handled 3 rows.
I want it to start in C4.

Any clever way to handle this copy/paste to automatically adjust
the rows I want to relate it to?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html