Hello, all!
I have searched every combination of terms I can think of, but I can't find a solution. If there is one, please send me the link.
I have an workbook which is used as a client tracking sheets (tax clients). As each client comes in, the information is added to columns in a sheet ("2013") as follows:
C - Client Name
D - Type (e.g. 1040)
E - Year
F - Date in
G - Status
And the rest of the columns are populated at different milestones finishing at:
S - Process Date
I need 2 counts. One is the number of "in-house" returns not completed. For that I have something that is working fine.
=COUNTA(C15:C600)-COUNTA(S15:S600)
The 2nd count I need is trickier. I need to count the universe of returns not yet completed. My main obstacles are that on the "2013" sheet, I have duplicates in column C (one client, multiple years) which need to be counted and we are also adding new clients.
Here is my solution:
1. I have a separate sheet ("WORKING") which lists the universe of clients from 2012 in column B and the number 2013 in column C. [Column B is also the Data Validation range for the drop down entry in sheet "2013" column C (Client Name)]
2. At the end of those 2 lists I have a formula pulling each Client Name and Year from the "2013" tab to "WORKING" columns B & C respectively for another 600 rows or so.
3. In the A column, I combine client and year with =IF(B1058<>0,CONCATENATE(C1058," ",B1058),0)
4. Then count the universe (excluding duplicates) with =SUMPRODUCT((A4:A1097<>"")/COUNTIF(A4:A1097,A4:A1097&""))
5. Last, I back out the count of completed returns.
My first problem is that I am getting a generic circular reference each time I open the workbook>
-- removed inline image ---
[TABLE="width: 297"]
<colgroup><col width="297"></colgroup><tbody>[TR]
[TD="class: xl63, width: 297, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
I think that this is coming from pulling data which comes from the same data validation range I'm using. But I don't know how to fix it.
I also would like to know if there is a simpler way to do this? I could do it in VBA quick & clean, but that would bug out the end users and they would likely muck something up. Same reason I can't use a 3D add-in.
Any suggestions would be greatly appreciated. I am also deeply grateful to everyone in this forum for helping me learn to code macros in VBA in the past.
[BTW, I would have posted the file, but apparently I don't have that right.]
I have searched every combination of terms I can think of, but I can't find a solution. If there is one, please send me the link.
I have an workbook which is used as a client tracking sheets (tax clients). As each client comes in, the information is added to columns in a sheet ("2013") as follows:
C - Client Name
D - Type (e.g. 1040)
E - Year
F - Date in
G - Status
And the rest of the columns are populated at different milestones finishing at:
S - Process Date
I need 2 counts. One is the number of "in-house" returns not completed. For that I have something that is working fine.
=COUNTA(C15:C600)-COUNTA(S15:S600)
The 2nd count I need is trickier. I need to count the universe of returns not yet completed. My main obstacles are that on the "2013" sheet, I have duplicates in column C (one client, multiple years) which need to be counted and we are also adding new clients.
Here is my solution:
1. I have a separate sheet ("WORKING") which lists the universe of clients from 2012 in column B and the number 2013 in column C. [Column B is also the Data Validation range for the drop down entry in sheet "2013" column C (Client Name)]
2. At the end of those 2 lists I have a formula pulling each Client Name and Year from the "2013" tab to "WORKING" columns B & C respectively for another 600 rows or so.
3. In the A column, I combine client and year with =IF(B1058<>0,CONCATENATE(C1058," ",B1058),0)
4. Then count the universe (excluding duplicates) with =SUMPRODUCT((A4:A1097<>"")/COUNTIF(A4:A1097,A4:A1097&""))
5. Last, I back out the count of completed returns.
My first problem is that I am getting a generic circular reference each time I open the workbook>
-- removed inline image ---
[TABLE="width: 297"]
<colgroup><col width="297"></colgroup><tbody>[TR]
[TD="class: xl63, width: 297, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
I think that this is coming from pulling data which comes from the same data validation range I'm using. But I don't know how to fix it.
I also would like to know if there is a simpler way to do this? I could do it in VBA quick & clean, but that would bug out the end users and they would likely muck something up. Same reason I can't use a 3D add-in.
Any suggestions would be greatly appreciated. I am also deeply grateful to everyone in this forum for helping me learn to code macros in VBA in the past.
[BTW, I would have posted the file, but apparently I don't have that right.]
