Excel 2007 : Need help with Formula

[srgtennis]

=IFERROR(INDEX('[March 2012 - Transfer loads.xlsx]FP CUT - Apples '!$A$3:$A$2000,SMALL(IF('[March 2012 - Transfer loads.xlsx]FP CUT - Apples '!$O$3:$O$2000=27,ROW('[March 2012 - Transfer loads.xlsx]FP CUT - Apples '!$A$3:$A$2000)-ROW('[March 2012 - Transfer loads.xlsx]FP CUT - Apples '!$A$3)+1),ROWS($AQ$5:$AQ41))),"")

This is the formula I am I using which is good, it takes these receiver numbers depending on what room number I put in 03 thru o2000 and sorts all the receiver # that are in room 27 and puts them in a column for me so all of the receivers are sorted by what room they are in.

I need it to sort even more, When it sorts it by room # that is good but then there are 3 different varieties in that room, I would like it to sort by rooom number and then sort them by variety in that room.

example: receiver 1, 2, 3 are Romes and receiver 4, 5, 6 are Empire but they are all in room 27. I would like it to put 1, 2, 3 in Column A and 4, 5, 6 in column B but they are still under the header of room 27.

I can get a workbook if that will help out. let me know

[Dennis7849]

Wow, it's time to move into the macro world. It makes things much cleaner, more efficient and easier to understand.

[srgtennis]

Don't know how to use macros

[srgtennis]

anyone have any ideas?

[zbor]

Upload example workbook.
Make sure to remove private data.

[srgtennis]

Here is a sample workbook

[darkyam]

One relatively simple way to do it would be to concatenate the type of apple and the room in a cell just to the right in March. You could even hide this column. Then simply change your lookup to go from "20" to "Empire20" in the next cell over.

[srgtennis]

that doesn't seem to work but I don't know why
thanks for the suggestion.

[darkyam]

Seems to work for me.

[srgtennis]

I just got it, I had to put "" around it. but then I will have to type that in a column and manually type that too, or how can I get it to be empire20 by itself?

[darkyam]

I'm not sure I understand your question. In March, column S concatenates apple type and room, so all you have to do is change it for each room and type in the second sheet. If you want it more automatic, you can replace "Empire20" with

Please Login or Register  to view this content.

with no quotations around it.

[srgtennis]

thanks for the help can't get it yet but I will keep trying with what you did.

[srgtennis]

anybody have any other ideas?

[srgtennis]

I need the first formula to be able to seperate by room and then variety? Anyone help