Hello All,
I work with the state of California (57 Counties). Each county will have a varying list of programs (ranging from 4-74). In the example, each program is required to generate a count.
I've created a template in excel to standardize a data report for all counties. Each county will run their reports and paste them on the proper sheets in the specified cell. From these multiple reports, a master table is generated per county with each programs data listed per row (abbrieviated example).
Master table
Program Has Data Counts
----------------------------------------
ABC Program YES 14
123 Program NO
DEF Program NO
456 Program YES 11
GHI Program YES 15
Here's my problem:
If I was to use a pivot report on this table, I could get a table of programs that have data, showing the count next to the program name. The pivot report decreases rows based on the filter criteria, so that your table is displaying only programs with data.
Is there a way to create this using formulas that can add or delete rows in excel. Since I'm creating a template, I can't format a pivot report to automatically filter programs before the data is pasted into the spreadsheet (I would have to use multiple pivot reports and they can't overlap or reposition based on the data). Is there a way to create the exact same effect by using a formula in cells.
I would like a finished product like this:
COUNTY REPORT
PROGRAMS WITH DATA
Program Has Data Counts
----------------------------------------
ABC Program YES 14
456 Program YES 11
GHI Program YES 15
COMMENTS ABOUT DATA
Text written here, two blank rows below table.
Since some counties will have as few as four programs total, and some as much as 75 total, I need a way to have the spreadsheet adjust the rows based on the criteria from the master table (which will be hidden on a different sheet). Is this possible??!!
Thanks you for your time; I apologize that this is lengthy.
Aspiring to new heights in Excel,
Andre
I work with the state of California (57 Counties). Each county will have a varying list of programs (ranging from 4-74). In the example, each program is required to generate a count.
I've created a template in excel to standardize a data report for all counties. Each county will run their reports and paste them on the proper sheets in the specified cell. From these multiple reports, a master table is generated per county with each programs data listed per row (abbrieviated example).
Master table
Program Has Data Counts
----------------------------------------
ABC Program YES 14
123 Program NO
DEF Program NO
456 Program YES 11
GHI Program YES 15
Here's my problem:
If I was to use a pivot report on this table, I could get a table of programs that have data, showing the count next to the program name. The pivot report decreases rows based on the filter criteria, so that your table is displaying only programs with data.
Is there a way to create this using formulas that can add or delete rows in excel. Since I'm creating a template, I can't format a pivot report to automatically filter programs before the data is pasted into the spreadsheet (I would have to use multiple pivot reports and they can't overlap or reposition based on the data). Is there a way to create the exact same effect by using a formula in cells.
I would like a finished product like this:
COUNTY REPORT
PROGRAMS WITH DATA
Program Has Data Counts
----------------------------------------
ABC Program YES 14
456 Program YES 11
GHI Program YES 15
COMMENTS ABOUT DATA
Text written here, two blank rows below table.
Since some counties will have as few as four programs total, and some as much as 75 total, I need a way to have the spreadsheet adjust the rows based on the criteria from the master table (which will be hidden on a different sheet). Is this possible??!!
Thanks you for your time; I apologize that this is lengthy.
Aspiring to new heights in Excel,
Andre
