2

Here is my spreadsheet. What I want to happen is: I enter data in the sheets marked Head, Neck, Shoulders etc.and that data to then be put in specific areas of LFR and Normal based on 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 two sheets but I will change that if this request is able to be performed.

Improve this question

1 Answer 1

Reset to default
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'"))

Improve this answer
4
  • That's the solution? When formulas become that large, to me it clearly indicates something is wrong about the logic. Nonetheless, thanks for posting an answer! !!
    – Jacob Jan Tuinstra
    Feb 5, 2013 at 11:58
  • This does help a lot as I know very little about spreadsheet formulas. Thank you for taking the time to look at it.
    – Nastrand
    Feb 5, 2013 at 16:44
  • I know the formula is large. He was in a hurry and I didn't have time to work more on it. I am still working with him to get that down to something more elegant.
    – William
    Feb 5, 2013 at 17:51
  • 1
    I moved all the data onto one sheet, creating a mini "database". That makes the query much simpler. Works much better now. Still need some refining. Here is the current formula. =query('Upgrades List'!A1:E200, "select * where D contains 'Vaults'",0)
    – William
    Feb 7, 2013 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.