Excel find and sort problem

  picklefactory 15:42 01 Sep 2006

Hello folks. Any Excel wizzes online today?
I have a sheet containing equipment details required for a job. It lists all items required, in detail, with part numbers, costs, suppliers etc. Some of these items may need to be purchased for a given project, some may already exist in house, I have a check box that determines this. What I want to do is search for all items with a check in the box and copy certain cells to a separate sheet to create a list of items requiring purchasing. This sheet is multi-function and due to other needs, I cannot use Vlookup, as I cannot sort in ascending order or have the checkbox as leftmost column.
Here's some more detail.
ColumnA = Description, ColumnB = Part No, ColumnO = Qty, ColumnP = Checkbox, ColumnQ = Supplier and ColumnS = Cost.
I want to search for all TRUE values in ColP and copy respective values in the same row from the remaining columns to a separate sheet in a nice tidy list.
I expect it will be some clever bit of code, unfortunately I have neither the brains nor experience to know how to create it. Any clues folks?

  VoG II 16:32 01 Sep 2006

Select the row 1 of the column with TRUE/FALSE, Data > Filter > Autofilter. Click the Filter arrow and select TRUE. Copy the filtered values and paste them on your other sheet.

  Noldi 16:32 01 Sep 2006

Can you not use Auto Filter for this?.


  Noldi 16:32 01 Sep 2006

Can you not use Auto Filter for this?.


  picklefactory 19:25 01 Sep 2006

Thanks folks, but not quite what I need. This workbook will be part of my department systems and, unfortunately, although I am not exactly brilliant at this sort of thing, I am a fair bit more advanced than my colleagues. I'm looking for something automatic and amateur proof that does all the work for us. Ideally as soon as a checkbox is ticked, the relevant data just cheerfully skips across to the next sheet and creates a nice tidy, smiley list that we can print off.
Might seem a little lazy, but you wouldn't believe how little pc knowledge some of our employees have.
I've read on a Google I found that I may be able to use Vlookup if I have the Range lookup value as False to avoid needing to sort. Not too sure about that myself, I would expect to still need checkboxes to be leftmost column, but I'll have a dabble.

  VoG II 19:28 01 Sep 2006

You possibly need a worksheet_change macro but if so you are looking at a fair amount of coding. How good is your VBA?

  picklefactory 19:43 01 Sep 2006

My VBA is such that I can tinker with existing to get it to suit my needs (Creating basic macro and butchering a bit etc), but I struggle to create from scratch, (I'm an engineer, I only spend a few hours a day in front of my pc). However, I think I have found a formula means of doing it. Found a similar problem elsewhere and was pointed in the direction of this.
click here= [/url]
I'm just in the process of tweaking it to work across separate sheets as the sample is all on one sheet, but it seems to be hitting the spot so far. I'll repost when I'm finished.

  picklefactory 20:02 01 Sep 2006

Woohoo!! That works a treat. Job done. One happy Picklefactory.
Thanks for your time and effort gents. I am sorted, I can now go home for a well earned drink or ten.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone