Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percent Complete? | Excel Discussion (Misc queries) | |||
Lookup worksheet name in separate workbook | Excel Discussion (Misc queries) | |||
How do I get a hyperlink to track specific data in a worksheet? | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Indirect reference from one worksheet to another | Excel Worksheet Functions |