OK this may be totally out of my league but I'm going to ask anyway!

[litesnsirens]

SO here is a brief overview. I'm a police officer working in a specialty unit and created a speadsheet years ago for my office to log our cases. I wrote formulas so that it would calculate stats on a separate sheet. A couple years ago we got a new boss who loved it. He has moved on to anther unit and wants me to create a spreadsheet for his new unit. But he has these ideas of what he would like it to do, I don't even know if it's possible and I fear that even if it is, it may be way over my head.

So on sheet 1 everyone will just enter the info from their investigations, I've created the necessary validations so it can collect stats etc. What this guy wants is a separate sheet for "cases before the courts" which I have in my current spreadsheet for my office but our admin assistant just keeps that up to date manually. The new idea is that if the column that I have labelled "charges" on sheet 1 has a value (indicating that a charge is laid and there will be a court date) that those values from the "charges" column along with values from other relevant columns will populate over to the second sheet.

The first problem I see is that because there won't be charges laid in every investigation, I will end up with the scenario of trying have for example values from rows 2, 7, 15, 21 etc populate into rows 1, 2, 3, 4 etc of the second sheet. And of course conversely looking at the columns I don't want values from every column, so I am going to try to take values from columns C, H, K, L, P, etc. and have them populate Columns B, C, D, E and F etc.

So I guess question 1 would be is this even possible? Question 2 would be if it is how complicated would it be to pull it off?

[Bob Phillips]

Sounds to me that you want a pivot table, you can show just the columns that you want, and filter the pivot by the rows with a value in the charge column.

[Sean Thomas]

You could always run a macro for sheet 2, either by sheet activated event or by a button.
This will then clear all data in sheet 2 and search sheet 1 for all cases due to go to court and list them one after the other using the correct columns.
If you upload an example of your data and what you want then it shouldnt be too difficult.

[TMS]

Yes, it should be possible. I assume there is a unique reference? If there is you can create an Array Formula to return the reference and, using that, have VLOOKUP formulae to return the rest of the relevant fields.

Can't do anything now but you could search for examples of "extract list of values matching criteria". This is a pretty good example that is well explained so you should be able to adapt it.

http://exceltactics.com/make-filtere...l-using-small/

Once you have your list, use VLOOKUP to get the rest.

Regards, TMS

[litesnsirens]

Thanks for the replies, it looks like I have some options. I will return to this thread when I get far enough into this and get more specific. In the meantime I will play with some of these ideas on a separate spreadsheet to see if I can get these things to work.

I really appreciate your time.