summarizing data to auto fill in another worksheet.

%3CLINGO-SUB%20id%3D%22lingo-sub-289348%22%20slang%3D%22en-US%22%3Esummarizing%20data%20to%20auto%20fill%20in%20another%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289348%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20around%2020%26nbsp%3Bworksheets%2C%20all%20the%20final%20values%20get%20consolidated%26nbsp%3Bonto%20page%231.%26nbsp%3B%20%26nbsp%3BPage%20%231%20has%20over%26nbsp%3B300%20rows%20and%26nbsp%3Bseveral%20columns%20that%20have%20the%20quantities%20and%20costs%26nbsp%3Bbrought%20into%20it%20by%20simply%20entering%20the%20formula%20(cell%20%3Dsheet2!E23).%20However%2C%20not%20all%20300%20rows%20have%20information%20in%20them.%20I%20want%20to%20add%20another%20sheet%20that%20will%20automatically%20list%20only%20the%20rows%20that%20have%20information%20in%20them%20from%20Sheet%231%20without%20leaving%20blank%20rows%20in%20between%26nbsp%3Bthe%20rows%20with%20data.%26nbsp%3B%20Can%20you%20tell%20me%20if%20this%20is%20possible%20and%20how%20it%20can%20be%20accomplished%3F%20I%20am%20a%20beginner%20in%20Excel%20so%20I%20will%20need%20a%20detailed%20explanation.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20is%20not%20possible%2C%20can%20all%20the%20rows%20on%26nbsp%3Bsheet%231%20without%20data%20in%20them%20be%20hidden%20automatically%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20time%20and%20effort.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-289348%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289368%22%20slang%3D%22en-US%22%3ERe%3A%20summarizing%20data%20to%20auto%20fill%20in%20another%20worksheet.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289368%22%20slang%3D%22en-US%22%3E%3CP%3EMichaelK%2C%20I%20would%20suggest%20you%20make%20yourself%20familiar%20with%20Power%20Query.%3C%2FP%3E%3CP%3EPQ%20can%20consolidate%20many%20worksheets%20into%20one.%20It%20can%20filter%2C%20sort%20and%20you%20can%20add%20new%20columns%20and%20lots%20more.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a workbook with around 20 worksheets, all the final values get consolidated onto page#1.   Page #1 has over 300 rows and several columns that have the quantities and costs brought into it by simply entering the formula (cell =sheet2!E23). However, not all 300 rows have information in them. I want to add another sheet that will automatically list only the rows that have information in them from Sheet#1 without leaving blank rows in between the rows with data.  Can you tell me if this is possible and how it can be accomplished? I am a beginner in Excel so I will need a detailed explanation. 

 

If that is not possible, can all the rows on sheet#1 without data in them be hidden automatically?

 

Thank you for your time and effort.

1 Reply

MichaelK, I would suggest you make yourself familiar with Power Query.

PQ can consolidate many worksheets into one. It can filter, sort and you can add new columns and lots more.