More excel 2007 wizardry

[Woden]

Can I create a table from specific data in another table?

I have a tab, lets call it Inbound, 'cos thats what it is, that lists every delivery to my site, around 250 a day with a bunch of details.

What I need to do is create a table in anothwer tab that lists only the rows that have K>30 AND F>17:00 (5pm). I don't need the whole row either, just specific columns.

Is there an easy way to automate this? It needs to be idiot proof for when I am not on-site, or do we stick to copy/pasting to a helper sheet?

Thanks

 

[adolf hamster]

The simplest way i can think would be to use conditional formatting to highlight rows that meet the criteria, only downside is it wouldn't list as a new sheet, just make it easier to find for the existing table.

In terms of copying/pasting i can only think of a macro as a way to do it, but my expereince with them is limited and mostly confines itself to copying a cell based on user input to another.

 

[Jim99]

Off the top of my head, you should be able to have a second sheet that runs IndexMatch formulae to capture all of K>30 from the first sheet, and then you could have a third sheet that runs IndexMatch to capture all of F>17:00 from the second sheet.

The results on that third sheet would be what you were after. And you could hide the second one.

 

[Mark M]

Could you not use an IF function? It would show gaps for anything didnt meet the criteria (ie the stuff that had been delivered) but you could apply a quick filter to it and sort of hide the blanks.