Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Sum Wizard with dynamic named ranges
Hello folks, Been scratching my head for a couple of hours trying to avoid having to resort to VBA. Hopefully, you can help me with the following... I've used the Conditional Sum Wizard to set the value of a cell depending on multiple conditions and that seems to work fine, but I'd like to be able to use some dynamic, named ranges instead of absolute cell references in my formula. Here's the formula that works with the absolute references, Code: -------------------- {=SUM(IF(Payments!$A$2:Payments!$A$113=DATEVALUE( "06/04/2004"),IF(Payments!$A$2:Payments!$A$113<=DATEVALUE ("05/04/2005"),IF(Payments!$H$2:Payments!$H$113=$A6,Paymen ts!$E$2:Payments!$E$113,0),0),0))} -------------------- What I'd really like to do is to replace the hard-coded reference to row 113 as addtional rows are appended. I thought my best approach would be to replace the cell range with a named range, as per the following, but it didn't work. Code: -------------------- {=SUM(IF(payments_a=DATEVALUE("06/04/2004"),IF(payments_a<=DATEVALUE("05/04/2005"),IF(payments_h=$A6,payments_e,0),0),0))} -------------------- Any suggestions? Thanks :) -- willee ------------------------------------------------------------------------ willee's Profile: http://www.excelforum.com/member.php...o&userid=31189 View this thread: http://www.excelforum.com/showthread...hreadid=508589 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of named ranges conditional to date? | Excel Worksheet Functions | |||
Dynamic Named Ranges | Excel Discussion (Misc queries) | |||
Problem with Dynamic Named Ranges | Excel Worksheet Functions | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |