2009/1/3 selenio <[email protected]> > > Hello everyone > > I'm looking a way to the following, I have a huge sheet1 like this: > > sheet1 > quantity | reference | item > 7 | 6190221 | item1 > | 3443220 | item2 > | 7244163 | item3 > 3 | 8900167 | item4 > | 3638812 | item5 > > And I have a sheet2 that currently shows the same because is done this way > =IF(ISTEXT("sheet1".A6);"sheet1".A6;""), but since sheet1 is growing > steadily, and data changes quite often I need to keep sheet2 clean showing > only items with quantity, what I expect is this: > > sheet2 > quantity | reference | item > 7 | 6190221 | item1 > 3 | 8900167 | item4 > > How I can do this with functions? > > Thanks. > > Hi,
If sheet2 is only used for showing the 'filtered' data as an view on sheet1 you might be able to solve that within sheet1 by using auto filter. 1 Go to sheet1 select the three columns. 2 Select AutoFilter from menu: Data->Filter->AutoFilter. Now an down arrow appears in column heading 3 Select the arrow in quantity header and choose "Standard Filter" 4 Select the condition '=' with value '-non empty-' When you what to see all rows in sheet1, just select 'All' in the auto filter. You can also use the data pilot for this 1 select all three columns 2 Select data pilot from menu: Data-> Data Pilot->Start (Take current selection as default) 3 Drag quantity, reference, item (in that order) to 'Row fields' section 4 Press 'More' 5 Select '-new sheet-' as 'Result to' , untag 'Total columns' and 'Total rows' 6 Press OK, now new sheet is shown. 7 Select Filter button (Field A1) and add filter quantity = '-not empty-' Your changes in sheet1 will be applied to the the data pilot (you might need to refresh it via Data->Data Pilot->Refresh Success, Erik
