google spreadsheet question about data linking between sheets

Question

Here is my spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AgmD6-Z4d2aVdE9pR095OTJrVE1wS3FIZzZpVFRJMmc#gid=4

What I want to happen is I enter data in the sheets marked Head, Neck, Shoulders etc.

I want that data to then be put in specific areas of LFR and Normal based off of what is in the location field of Head, Neck, Shoulders etc sheets.

For example, Under the Helm tab there is: Item Level Item Name DPS Increase Item Location 483 Firebird's Helmet +2171.93 Sha of Fear - Terrace of Endless Spring (LFR) 483 Crown of the Doomed Empress +211.27 Grand Empress Shek'zeer - Heart of Fear (LFR)

I want the LFR Raids sheet to list Firebird's Helmet under the Terrace of Endless Spring section automagically and then it would put Crown of the Doomed Empress in the Heart of Fear section of the LFR but it would also grab anything that I have input from Terrace of Endless Spring from the other sheets (neck, legs, gloves, etc) in there as well.

My formatting is different a little from the 2 sheets but I will change that if this is request is able to be performed.

Answer 1

I have worked on this issue and this is as far as I got with limited time. I'm sure it could be made simpler but maybe this will get you on the right track!

Paste this in the first column of the "Sha of Fear..." [A22]

This one query will do all sheets but only one at a time (by changing the Index after the Choose statement.

so, for the Neck change it index to a 2 and so on... 13 total sheets. A bit tedious but it will work.

=Choose(1,query(Head!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Neck!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Shoulders!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Back!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Chest!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Wrists!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Gloves!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Belt!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Legs!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Feet!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Rings!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Trinkets!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"),query(Offhand!A2:E90, "select * where D contains 'Sha of Fear - Terrace of Endless Spring'"))