Track progress to lookup percent complete on another worksheet.
I am trying to track progress in a Gap Analysis file. In doing so I would
like to calculate the percent of fields completed for each column in a seperate workbook updated frequently (contains 1500+ different plan names) . This report file as of now only contains a percent of the data needed and will be updated frequently. I am trying to keep track of the percents. Currently I am going in and using =counta(e2:e1512)/counta(a2:e1512) - This is counting from a different file from the Gap Analysis My Gap Analysis file contains fields with same name as the file i am counting for so I know I can tie the two together somehow either with a vlookup or some other function, I just do not know how to go about doing this correctly? Below is an example. Example: (Gap Analysis File) Would like to see how many of fields complete from Report file A | B 1 Report Field | Percent Complete 2 Plan 100% <- Always will be 100% - (Total number of plans) 3 State ? <-Should contain 75% 4 Tier ? <-Should contain 50% 5 Region 75% (=counta('ReportFile'!D2:D5)/counta('ReportFile'!A2:A5) 6 Type ? <- Should contain 0% (Report File) Users go in and enter data that is not currently there. A | B | C | D | E | 1 Plan State Tier Region Type 2 Name1 CA "No Data" South "No Data" 3 Name2 "No Data" 2 "NoData" "No Data" 4 Name3 PA 3 North "No Data" 5 Name4 TX "No Data" West "No Data" * "No Data" contain empty fields How could i calculate this automatically without calculating each column seperately in my Gap Analysis file? I assume there is an easier way to go about doing this... Any suggestions or a direction to go in? |
All times are GMT +1. The time now is 02:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com