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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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


QTYPART #HEIGHTLINESDESCRIPTIONUNIT PRICETOTAL PRICE
Rail Drawings 10.00 -
H-Rail & 1 Line Drawings 10.00 -
Assembled Rail 32"2
10.00		 -
Straight Post w/ rod32"2 10.00 -
Sloped Post w/ rod32"2 10.00 -
Wall Plate32"2 10.00 -
Assembled Rail 36"2 10.00 -
Straight Post w/ rod36"2 10.00 -

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
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
  • Locked
  • Question
Still need help on a daily summary sheet
Replies
1
Views
282
Fluff
Fluff
G
  • Question
Summary Sheet lookup
2
Replies
16
Views
215
awoohaw
awoohaw
P
  • Question
Complete Summary Table with data from multiple sheets
Replies
6
Views
284
pioshelby1980com
P
U
  • Question
Summary data from Multiple sheets
Replies
4
Views
294
KRice
K
K
  • Question
Formula needed to look up specific name and enter data from specific column within same row
Replies
1
Views
182
awoohaw
awoohaw

Forum statistics

Threads
1,214,606
Messages
6,120,485
Members
448,967
Latest member
visheshkotha

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