Summary Sheet

D

dschemmel

New Member
Joined
Feb 1, 2017
Messages
20
I have an excel workbook with 8 sheets that could potentially have data added and could potentially not. I want to create one summary sheet that cleanly displays all rows that data was entered throughout the sheet. The sheets are used for quoting projects so I would want all of the information to come over but only if a quantity is entered.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Can you provide an example showing the data that may or may not be added? Sounds like you can accomplish this via some simple IF function work but need to better understand what you are asking.
 
Upvote 0
I have 8 pages with tables containing this type of information. Oftentimes for quoting purposes, multiple pages will need to be used. I want all of the information filled in to automatically go to a summary page. For example, if all of the QTY's are blank the summary sheet should be empty, but if someone enters say 20 in the QTY column for rail drawings I would like that to automatically populate in the summary page. I want only the rows that feature values in the QTY column to be copied over to the summary sheet. I also would like there to be no blank rows.

My thought process is to somehow have excel read through the sheets starting with the first one and moving to the right to display every row with a QTY. Did that make any sense?

*Prices have been changes


[TABLE="width: 655"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]QTY[/TD]
[TD]PART #[/TD]
[TD]HEIGHT[/TD]
[TD]LINES[/TD]
[TD]DESCRIPTION[/TD]
[TD]UNIT PRICE[/TD]
[TD]TOTAL PRICE[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Rail Drawings[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 10.00[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]H-Rail & 1 Line Drawings[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 10.00[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Assembled Rail [/TD]
[TD]32"[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]
10.00[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Straight Post w/ rod[/TD]
[TD]32"[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] 10.00[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Sloped Post w/ rod[/TD]
[TD]32"[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] 10.00[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Wall Plate[/TD]
[TD]32"[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] 10.00[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Assembled Rail [/TD]
[TD]36"[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] 10.00[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Straight Post w/ rod[/TD]
[TD]36"[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] 10.00[/TD]
[TD] - [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Given you have multiple sheets, I'd suggest writing a fairly simple VBA script to Go through each of the sheets, row by row and copy cell data to your target report when it finds Qty data. Doing this solely in excel without VBA is not straight forward.
 
Upvote 0
I'm not sure, but this may help:

Code: 
[COLOR=#252C2F][FONT=Courier]Sub Transfer()[/FONT][/COLOR]Dim sht As Worksheet
Dim target As Worksheet
Dim rng As Range
Dim targetrng As Range
     
    With ThisWorkbook

'clear previous entries
Select Case Sheets("Summary").Range("A2") <> ""
Case True
Sheets("Summary").Range("A2", Range("A65536").End(xlUp)).EntireRow.Clear
End Select

Set target = .Worksheets("Summary")

   For Each sht In .Worksheets
       If Not sht.Name = target.Name And sht.Name <> "Desired Summary" Then
          Set rng = sht.UsedRange.Offset(1, 0).Resize(sht.UsedRange.Rows.Count - 1)
          'use a combination of offset And resize To ignore first 1 rows
           Set targetrng = target.Cells(65536, 1).End(xlUp).Offset(1)
           rng.Copy targetrng
        End If
   Next sht
  End With
  
Set rng = Range("a1", Range("d" & Rows.Count).End(xlUp))
rng.AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="=SUM:"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
rng.AutoFilter
 [COLOR=#252C2F][FONT=Courier]End Sub[/FONT][/COLOR]

I also found this which may help:

Code: 
[COLOR=#101094][FONT=inherit]Private[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] CopyNonBlank[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#303336][FONT=inherit]    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] destWS [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]' Destination worksheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] destWS [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Action Summary"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"C2:C32"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Len[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]' if cell is not empty lenght would be greater than 0[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            destWS[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value
            i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]
 
Last edited:
Upvote 0
Thanks sgroath. I will check into that and see if anyone on the team here knows how to do this. Any other suggestions as we tackle this? Best practices, what to avoid, etc.? Any and all help is greatly appreciated!!

One key to note is that I will not actually be using this spreadsheet. Most of the people who will be are inexperienced in excel so if they have to run a complicated macro or anything they will not do it.
 
Upvote 0
sgeng4 said:
I'm not sure, but this may help:

Code: 
[COLOR=#252C2F][FONT=Courier]Sub Transfer()[/FONT][/COLOR]Dim sht As Worksheet
Dim target As Worksheet
Dim rng As Range
Dim targetrng As Range
     
    With ThisWorkbook

'clear previous entries
Select Case Sheets("Summary").Range("A2") <> ""
Case True
Sheets("Summary").Range("A2", Range("A65536").End(xlUp)).EntireRow.Clear
End Select

Set target = .Worksheets("Summary")

   For Each sht In .Worksheets
       If Not sht.Name = target.Name And sht.Name <> "Desired Summary" Then
          Set rng = sht.UsedRange.Offset(1, 0).Resize(sht.UsedRange.Rows.Count - 1)
          'use a combination of offset And resize To ignore first 1 rows
           Set targetrng = target.Cells(65536, 1).End(xlUp).Offset(1)
           rng.Copy targetrng
        End If
   Next sht
  End With
  
Set rng = Range("a1", Range("d" & Rows.Count).End(xlUp))
rng.AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="=SUM:"
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
rng.AutoFilter
 [COLOR=#252C2F][FONT=Courier]End Sub[/FONT][/COLOR]

I also found this which may help:

Code: 
[COLOR=#101094][FONT=inherit]Private[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] CopyNonBlank[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] destWS [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]' Destination worksheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] destWS [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Action Summary"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Long[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"C2:C32"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Len[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#858C93][FONT=inherit]' if cell is not empty lenght would be greater than 0[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
            destWS[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value
            i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] i [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
        [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] cell [/FONT][/COLOR]</code>[COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]
Click to expand...

I did not see this before my previous comment. i will test this out. Thanks!
 
Upvote 0
good example - you should be able to assign the code to a button to simplify the activity for the users.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Toying with idea of making a fullfledged tax return computation for 1040 form in excel
Replies
4
Views
109
Joe4
Joe4
M
  • Question Question
Protecting Sheet from users but permitting Power Automate
Replies
1
Views
370
ExcelToDAX
E
S
  • Solved
Unknown/Undeletable VBA Microsoft Excel Object
Replies
3
Views
374
Alex Blakenburg
A
FloatedWheat
  • Question Question
Protect Sheet with Different User Permissions
Replies
3
Views
514
iggydarsa
iggydarsa
W
  • Question Question
Mediocre Excel User Seeking to Improve on Bills Sheet
Replies
2
Views
200
cooleomter
C

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top