Excel 2003 functions
 

I would like $D27 in this function to increment the row by 1 as I copy to
other columns to the right of it. How do I do that?

=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=$D$27),$C7,0)

I would appreciate any help I can get on this one.

Thanks.

=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=INDIRECT(CHAR(
64+COLUMN(D1))&"27")),$C7,0)


--
HTH

Bob Phillips

"dacm19" wrote in message
...
I would like $D27 in this function to increment the row by 1 as I copy to
other columns to the right of it. How do I do that?


=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=$D$27),$C7,0)

I would appreciate any help I can get on this one.

Thanks.

Try:

=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=INDIRECT("D"&C
OLUMNS($A$1:A1)+26)),$C7,0)

which should do what you want when you copy across, if I've read you
correctly

Replaced "$D$27" in your formula by:
INDIRECT("D"&COLUMNS($A$1:A1)+26))

When you copy across, "$D$27" will point to D28, D29 and so on
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"dacm19" wrote in message
...
I would like $D27 in this function to increment the row by 1 as I copy to
other columns to the right of it. How do I do that?


=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=$D$27),$C7,0)

I would appreciate any help I can get on this one.

Thanks.

Thanks. I have never used this function before. However, it did not resolve
the problem. In COLUMNS($A$1:A2), A2 changes to B2, B3,etc. as I copy
horizontally in the worksheet. I need the D27 to change to D28 etc. as I copy
horizontally.

What I have is random sales entered into a worksheet with changing dates as
sales occur. Each sale item has a code. The number of the items sold is also
entered. I am trying to get the worksheet to calculate by month how many
items of each code were sold. Columns are Date, Code, Qty Sold. Say 1/1/05,
1, 10. Date, code, qty. Because there are many sales per day per month, I
need a way to calculate by month, how many of Code 1 were sold, of Code 2
were sold, etc.

I add this information to see if it helps you to see what the problem is
that I have.

I am very appreciative of the help.

"Max" wrote:

Try:

=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=INDIRECT("D"&C
OLUMNS($A$1:A1)+26)),$C7,0)

which should do what you want when you copy across, if I've read you
correctly

Replaced "$D$27" in your formula by:
INDIRECT("D"&COLUMNS($A$1:A1)+26))

When you copy across, "$D$27" will point to D28, D29 and so on
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"dacm19" wrote in message
...
I would like $D27 in this function to increment the row by 1 as I copy to
other columns to the right of it. How do I do that?


=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=$D$27),$C7,0)

I would appreciate any help I can get on this one.

Thanks.

try the following, but you need to have the analysis toolpak add-ininstalled

replace E1 in the COLUMN() function with the address of a cell in the column
where this formula is to start


=IF(AND(EOMONTH($B$1,0)=EOMONTH($A7,0),$B$7=OFFSET ($D$27,COLUMN(E1)-5,0)),$C7,0)

"dacm19" wrote:

I would like $D27 in this function to increment the row by 1 as I copy to
other columns to the right of it. How do I do that?

=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=$D$27),$C7,0)

I would appreciate any help I can get on this one.

Thanks.

"dacm19" wrote:
... I need the D27 to change to D28 etc. as I copy horizontally...

But that is exactly how the suggested replacement will work in your formula
!??

Try this simple experiment to convince yourself

Put the numbers 1,2,3,4 into D27:D30

Now put in any cell other than D27:D30,
say in E3: =INDIRECT("D"&COLUMNS($A$1:A1)+26)

E3 will return what's in D27, i.e.: 1

Now copy E3 across to H3,
you'll see that E3:H3 returns what's in D27:D30
which is what you want

Give the earlier suggested formula (below) another try:
=IF(AND(AND(YEAR($A7)=YEAR($B$1),MONTH($A7)=MONTH( $B$1)),$B$7=INDIRECT("D"&C
OLUMNS($A$1:A1)+26)),$C7,0)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----