Excel 2003 functions

D

dacm19

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.
 
B

Bob Phillips

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

Max

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
 
D

dacm19

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.
 
D

Duke Carey

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)
 
M

Max

dacm19 said:
... I need the D27 to change to D28 etc. as I copy horizontally...
Click to expand...

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)
 
Top