Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blanks in a Summary Sheet
I have created a worksheet that allows me to input various types of data,
which direct inputs the data to a Summary Sheet (separate tab). Is there a way to have the Summary Sheet be more condensed by eliminating the blank (empty) information from the Data sheet (not all of the data areas will be filled in every time). Any help is appreciated! Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blanks in a Summary Sheet
In a column to the right of your data area (eg column H) in your
Summary sheet, you can have a simple formula which reproduces the values you have elsewhere, e.g.: =D5 (in H5) if that is where your data starts. The formula can be copied down. Then highlight column H and Data | Filter | Autofilter (check) to apply a filter to cover column H only. Then in the filter pull-down select Custom | does not equal | 0 (zero) to hide the unnecessary rows. Hope this helps. Pete On Jul 23, 2:42 pm, thaenn wrote: I have created a worksheet that allows me to input various types of data, which direct inputs the data to a Summary Sheet (separate tab). Is there a way to have the Summary Sheet be more condensed by eliminating the blank (empty) information from the Data sheet (not all of the data areas will be filled in every time). Any help is appreciated! Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blanks in a Summary Sheet
Another approach is illustrated in this quick sample:
http://www.savefile.com/files/911334 Pull over only source lines with key col filled.xls Assuming source table in cols A to C in sheet: Input, with key col = col A, data running in row2 down In sheet: Summary, B1:D1 carries the same col headers as in A1:C1 in Input In A2: =IF(Input!A2="","",ROW()) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Input!A:A,SMA LL($A:$A,ROWS($1:1)))) Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of data in "Input". Hide away col A. Cols B to D returns the required results from "Input", ie only lines from the source table where the key col A is filled, with all lines neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "thaenn" wrote: I have created a worksheet that allows me to input various types of data, which direct inputs the data to a Summary Sheet (separate tab). Is there a way to have the Summary Sheet be more condensed by eliminating the blank (empty) information from the Data sheet (not all of the data areas will be filled in every time). Any help is appreciated! Tom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blanks in a Summary Sheet
Pete, your advice works, but not exactly what I am looking for...I guess that
I am looking for something more automatic (with formulas). Is there a way (perhaps through the use of other worksheets) that this "summary" information can be consolidated? Thanks "Pete_UK" wrote: In a column to the right of your data area (eg column H) in your Summary sheet, you can have a simple formula which reproduces the values you have elsewhere, e.g.: =D5 (in H5) if that is where your data starts. The formula can be copied down. Then highlight column H and Data | Filter | Autofilter (check) to apply a filter to cover column H only. Then in the filter pull-down select Custom | does not equal | 0 (zero) to hide the unnecessary rows. Hope this helps. Pete On Jul 23, 2:42 pm, thaenn wrote: I have created a worksheet that allows me to input various types of data, which direct inputs the data to a Summary Sheet (separate tab). Is there a way to have the Summary Sheet be more condensed by eliminating the blank (empty) information from the Data sheet (not all of the data areas will be filled in every time). Any help is appreciated! Tom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blanks in a Summary Sheet
Max:
I am somewhat confused by your formulas... can you break it down (dumb it down) for me. Thanks. "Max" wrote: Another approach is illustrated in this quick sample: http://www.savefile.com/files/911334 Pull over only source lines with key col filled.xls Assuming source table in cols A to C in sheet: Input, with key col = col A, data running in row2 down In sheet: Summary, B1:D1 carries the same col headers as in A1:C1 in Input In A2: =IF(Input!A2="","",ROW()) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Input!A:A,SMA LL($A:$A,ROWS($1:1)))) Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of data in "Input". Hide away col A. Cols B to D returns the required results from "Input", ie only lines from the source table where the key col A is filled, with all lines neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "thaenn" wrote: I have created a worksheet that allows me to input various types of data, which direct inputs the data to a Summary Sheet (separate tab). Is there a way to have the Summary Sheet be more condensed by eliminating the blank (empty) information from the Data sheet (not all of the data areas will be filled in every time). Any help is appreciated! Tom |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminating Blanks in a Summary Sheet
Does the sample illustrate what you have in mind?
In the sample, in sheet: Summary, only lines from the source table (in sheet: Input) where the key col A is filled (ie col A is not left blank) would be pulled over, with all lines neatly bunched at the top. Play with the sample. If you delete the entries in Input's col A, the associated lines will disappear from Summary. Conversely, if you key-in something into Input's col A, then the associated line(s) will appear in Summary. Summary's col A formulae act as criteria to flag arb row numbers for lines in Input which are not blank. The other formulas in Summary's cols B to D then reads the criteria col A's flags to return the associated lines from Input, neatly packing all these lines at the top w/o any in-between blank lines. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "thaenn" wrote: Max: I am somewhat confused by your formulas... can you break it down (dumb it down) for me. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
Fill in Blanks for 1099-Div Summary Form, Office 2003 | Excel Worksheet Functions | |||
Summary Sheet | Excel Worksheet Functions | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) | |||
Summary sheet | New Users to Excel |