Search for column header and find all values and their row numbers in that column

Hi
Im trying to set up a find function so that I can look for a column header on a table within a sheet, and then to find in that column all the 'complete' or 'in progress' tasks. Every task has an ID number so i then need to be able to identify the ID no from each row with 'complete' in and copy those to another sheet in the correct place according to the ID.

Hope this is OK to understand, ive been looking at this for ages now and getting very muddled

attach please a sample of your file with comments

test.xlsm

Hi thanks for responding, Here is the file, I have put some comments into the VB code, I started to get lost at the end

sheets cell1 cel2 ... are the result You want ?

Originally Posted by patel45

sheets cell1 cel2 ... are the result You want ?

Sheets "cell 1", 2, 3 would be in another file. So if I was to change the status of say fault 1 to closed, I would want it to update with any comments in the correct sheet

Originally Posted by watersev

hi woody83, if I uderstood your requirement correct basing on the attachment file provided, option to make it, please check attachment, press "Sheet update"

Hi watersev,

Thanks for the reply,
your macro looks up the cell number correctly, but I also need it to look up the fault number and paste over the correct row in the other sheets. Each time the macro is run i dont want to add more faults at the bottom, just look for the cell number an dfault number, and then copy in any updates to the status etc.

Thanks for your help

as soon as you have a unique identifier - field Fault # - you could use formulas only approach, please check attachment, try to change Status in Overall sheet

Hi,

formulas in the cells column will be overwritten as soon as the induvidual cell data is copied in, so the lookups wont work.

The issue I have is that there are in total 10 files, that are on different PC's when a fault is created on one of those 10 files, I have a macro that copies the last row, looks for the cell number in the master file, then also adds that to the overall list where a fault number is assigned.

I was just after a piece of code that looks for the status value, and also the fault number and if it is complete, that row ONLY is copied to the correct fault on the correct cell number. This means that the master file is always up to date.

The spreadsheet I uploaded is a much simpler version of what I have, I am able to edit code as long as it is close to what I need, sorry I wasnt a bit clearer.

Thanks for your help

please check attachment