• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Named Ranges for Ranges with Blank Cells

Hello Everyone!
Attached is a sample worksheet of the type of data I'm working with.

My sample attached is using a checkbook register as the source data table. I wish to create a 'dashboard' that shows the sum or income, by category (paycheck, freelance) for a specific year. I thought I had this working, but once I began adding rows to the source data (checkbook register), I would get the #VALUE! error.

Below shows the four dynamic named ranges. The last two named ranges are the ones that have blank cells in the columns. I believe these are the ones that are the problem. As the data grows, the other two named ranges grow with the table. Conversely, the last two named ranges do not. In the attached sample, I crreated the table $A1:$F17 and everything worked. Then I added row 18 and got the #VALUE! error.

Thank you in advance for your consideration!

CHECKREG=CHECK!$A$3:$A$17A3:A17
CHECKDATE=CHECK!$C$2:$C$17C3:C17
CHECKDEP=OFFSET(CHECK!$D$1,1,0,SUMPRODUCT(MAX((CHECK!$D:$D<>"")*ROW(CHECK!$D:$D)))-1,1)D3:D17
CHECKWDL=OFFSET(CHECK!$E$1,1,0,SUMPRODUCT(MAX((CHECK!$E:$E<>"")*ROW(CHECK!$E:$E)))+1,1)E3:E17
 

Attachments

  • Dynamic Named Ranges.xlsx
    14 KB · Views: 2
As an FYI, another formula that seemed to work for the CHECKREG and CHECKDATE named ranges are as follows:
CHECKREG=CHECK!$A$2:INDEX(CHECK!$A:$A,COUNTA(CHECK!$A:$A))A3:A17
CHECKDATE=CHECK!$C$2:INDEX(CHECK!$C:$C,COUNTA(CHECK!$C:$C))C3:C17
 
i have made small changes on your formula, instead of refer column D:D AND E:E to measure the length i used A:A column where always data will be filled.

check attached file.
 

Attachments

  • Dynamic Named Ranges.xlsx
    14.7 KB · Views: 5
Back
Top