Worksheet that acts like a grouped report

[VirtualHuck]

Hello, I have a workbook that has pricing listed for different groups. There can be up to something like 10 items per group, and around 6 different groups. I was hoping there was a way to show all the groups and items within each group, but automatically have the blank rows removed (basically a readable report of the data that fits on a single printed page).

After trying a number of things, my closest solution was to have one sheet as the source data, then a separate worksheet that referenced the source data (So Output!A1 is set to equal Source!A1, etc.) 1, and applied a filter/sort on the output worksheet to remove the blank spaces.

The primary issue I came up against is that I'm hoping to make the formatting of the output worksheet look nice (otherwise all the blanks rows would be fine). If I format the source data, the formatting doesn't carry over into the referenced cells on the output worksheet. Alternatively, I can't seem to format the output to do something like have the headings in bold because the sort order/length of data in each group changes (maybe I just need to work out some complex conditional formating on the output data). The other issue is that I couldn't seem to find a way to get the output table to automatically update after changing the source data (guessing there is some way to force an automatic re-sort/filter?). I can keep plugging along and trying to resolve these issues, but wondering if I'm missing on a smarter/simpler approach.

As another shot of explaining what I am trying to do in a program I'm more familiar with: In Access, I would create a table with the data, then have a report that is set to Group By on the field I want. There are a number of reasons why I'm really trying to get this to work in Excel and not Access for this particular project...


*******Example entry Data (the actual data set and calculations are a lot more complex):*******
******(this data is seperated into rows and columns on the actual worksheet)******
Walls:
Standard Wall 100 @$100 = $10,0000
Half Wall 50 @$30 = $1,500
<empty row if another is needed>
<same empty row if needed another 5 or 10 times....>

Floor:
Big spans 500 @$10 = $5,000
<empty row if another is needed>
<same empty row if needed another 5 or 10 times....>

Roof:
Metal Roof 1,000 @$1 = $1,000
Shinlge Roof 500 @$1 = $ 500
<empty row if another is needed>
<same empty row if needed another 5 or 10 times....>

Totals
Sub Total =$18,000

*******Example Output Data (formatted and no unwanted empty rows):*******

Walls:
Standard Wall 100 @$100 = $10,0000
Half Wall 50 @$30 = $1,500

Floor:
Big spans 500 @$10 = $5,000

Roof:
Metal Roof 1,000 @$1 = $1,000
Shinlge Roof 500 @$1 = $ 500

Totals
Sub Total =$18,000

I'm new to the forum, and I did a quick search for similar questions and didn't find anything. I'm sorry if this has been answered before and I missed it.
Thanks!

 

[Taul]

Hi,
Welcome to the board.
It is possible to use formula or VBA to transfer the data to another page ready for printing. Formatting can be included as part of the routine.
Before we do that, have you tried just using the AutoFilter? I think it would be a simpler approach.

If you set up the page, formatted as you need but use the word "Spare" in the rows you are not using.
Use the AutoFilter to remove "Spare" from the filter list but leave the "Blank" rows ticked. The images below should explain the sequence.
Image 1 = basic layout
Image 2 = de-select the word "Spare"
Image 3 = The final page ready for printing

In image 3, the blue row numbers indicates the filter is active

 

[VirtualHuck]

Thank you Taul for the help and your solution is probably the simplest and best option for the question I asked. As I worked on the document, other issues popped up, such as sub-totals and notes between groups, etc.

I ended up doing a very similar thing, but using VisualBasic (as you noted could be done) without using the AutoFilter. I had my data page setup best for data entry (error trapping, drop down lists, etc., notes about what data to enter, etc.) and with all the "spare" rows for each section that I might ever need.

On the "report" worksheet, for each separate section, I pointed to each cell that I wanted to show from the data page including the spare rows (all the data on the report row has to be on one row of the data worksheet). I was able to do this section by section, really fine-tune the look, add notes or sub-totals under each section that made the report more readable, etc.. In short, I ended up with a report that was perfect, just really long as it shows each "spare" row.

To hide the un-used rows, I did a simple calculation cell off to the side of the report page for each row (actually it is in a narrow 1st row with white text so it is "invisible"); If quantity cell is >0 then "Show Row", otherwise leave blank. For the rows that are always shown such as subtotals and notes under each section, I manually entered the text "Show Row". I then wrote a simple vb script to cycle through all the rows and set the row height to 0 (hide the row) if the calculation cell didn't say "Show Row", otherwise set to a normal height. Once I flush out all the last details, I'll set that vb code to run when the report sheet gets focus. That way, anytime someone goes to the report worksheet, it will be ready to print based on the data entered.

Thanks again Taul.

 

[Taul]

Hi,
Thank you for the feedback.
I'm pleased you have managed to find a solution, it sounds like you have been doing some investigation with VBA.

Just in case you choose to use the Filter approach, in place of a Total sum like this [=sum(E4:E21)] you could use Subtotal.

=SUBTOTAL(109,E4:E21)

The SUBTOTAL will only add up the filtered (visible cells), so it should adjust to the values you need to show.

good luck
Paul.

 

[VirtualHuck]

Thanks for the note about the SUBTOTAL function, I somehow was not aware it existed. That will be helpful in many other situations. It's amazing how some basic things can be missed no matter how many years you have been working on a program .