Yodelayheewho
Member
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!
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$17 | A3:A17 |
CHECKDATE | =CHECK!$C$2:$C$17 | C3: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 |