Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
"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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
quattro converter for Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 Opens Up Every File in My Documents | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 back to Excel 2002 | Excel Discussion (Misc queries) | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) |