Lookup a list of cells from a certain sheet, dependent on the value in a correlating cell

[xcen92]

Hi!

I asked for something similar to this a little while ago, but a more complicated version than what I'm looking for now. I tried to simplify the one I was given by a very helpful member of this forum but I'm no way near their level and couldn't work out how to do it. Is someone could help me write a code for this function that would be great - but as simply as possible so that I can understand how it has been constructed and how to edit it in the future...?

In one sheet of my spreadsheet I have reference numbers on column A. These are linked to orders which are all recorded on an entirely different system. They are manually inputted into the spreadsheet. In column K There is a drop down for the status of this order at completion - whether it has gone ahead, if it is currently pending etc. One of these statuses is "Converted".

I have set up some lovely conditioning which means that when an order changes to Converted in the K column, the reference number in the A column highlights yellow, which is nice for glancing down a list. I now, however, need to somehow lookup all of those reference numbers in A that are set to Converted in K and produce a list of them on a separate sheet. I don't want them highlighted or anything - just plain text (I don't want them to have any aesthetic conditioning if that's possible - just automatic). It needs to pick up ALL of the reference quotes with Converted in the K column and ONLY those, and it cannot just miss lines - they need to all be one under the other on the new sheet.

If possible, this needs to be a case of a single code that I whack into the top cell of the list on the new sheet and the rest files in underneath - no copy and pasting and no dragging down as we don't know how many Converted's there will be in one month and if we have to drag I will have to keep going into the spreadsheet to keep dragging it. That being said, a dragging down is a possible second if that's the only way it can be done - I'll just have to drag it as long a way down as I can to limit how much maintenance needs to be done all the time. But yeah, so, single entry would be best and the dragging is a less enthusiastic option two.


Please can someone let me know how I can do this?

Many thanks!!

[boopathiraja]

HI, Can you post a small sample in excel file ?

[popipipo]

Without seeing the file I should say try to solve it with a pivot table

[xcen92]

Thanks for the quick responses!

Never heard of a pivot table :S

I was hoping I would be able to do it with an IF function as I know how to do those for the most part.

[Glenn Kennedy]

I think that this is what you need. It's an Array formula and needs to be set with CTRL + SHIFT + ENTER and not just enter.

[popipipo]

Take a look at this.

[boopathiraja]

HI, ref the attachment.. where C2= Status

Please Login or Register  to view this content.

[Glenn Kennedy]

Three totally different solutions for you to choose from (so far!!).

[xcen92]

Lol thanks everyone!

Okay the one from boopathiraja - For some reason I can't get it to work when I edit the cells and sheet names to the actual thing on the real spreadsheet - it keeps getting upset and saying that the formula doesn't work.

The one from popipipo - looks awesome and I will definitely be using it in the future because to be able to do all of that in one table is very handy and I have an idea where I'm going to be using that at a later stage with someone that can actually use all of its features- for this I just need something a little simpler atm, but thanks anyway!

The one from Glenn Kennedy - thanks very much - I think you helped me before! Each time I put it into the spreadsheet and hit either Enter or Ctrl Shift Enter it keeps bring up a Save box? Is that right?

[Glenn Kennedy]

Hold down ctrl and shift and then it enter...

[xcen92]

Yep no I tried that - still get the Save box pop up? Not sure how that works... :S

[xcen92]

HI, ref the attachment.. where C2= Status

Please Login or Register  to view this content.

How do I edit this one? Because when I try to use it it gets upset and says that the formula is invalid... I'm obviously editing it wrong...?

The coloured parts were the only bits that I changed. But it came up with an error message? Do I need to change anything else to make it fit to my spreadsheet?

Where my reference numbers are
Where my converted status' can be found.
Where I want my retrieved reference numbers to start listing from?

=IFERROR(INDEX(Sheet2!$A$2:$A$23,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$23)/(Sheet2!$B$2:$B$23=Sheet1!$C$1)-1,ROWS($A$1:A1))),"")

I basically changed it to -

=IFERROR(INDEX(Outbound Call Data!$A$18:$A$7500,AGGREGATE(15,6,ROW(Outbound Call Data!$K$18:$K$7500)/(Outbound Call Data!$K$18:$K$7500=November 2014 Stats!$B$32)-1,ROWS($A$1:A1))),"")

[boopathiraja]

Hi, you if try before 2010 version it won't work, your profile says as 2013, if so thats not a problem, may be check out for parenthesis closed correctly in this part " AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$23)/(Sheet2!$B$2:$B$23=Sheet1!$C$1) "

[xcen92]

Yep definitely not the version issue. I've checked all the parenthesis and they seem right. Each time I try it just comes up saying "The formula you typed has an error".

[Glenn Kennedy]

Regarding the array formula. Have you typed the curly brackets/braces in? If so, don't. Select the cell. Hit F and then CTRL + SHIFT +ENTER.

An array formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Glenn Kennedy]

Another thought... are you hitting S instead of SHIFT????

[boopathiraja]

Hi, can you repost a sample with formula what you used with original references but with minimized data

[xcen92]

Okay I've typed it in and it's opened the save folder again. But when I close the save folder the {}s are there so it must be working. I then changed some of the data it should be picking up, however, and it doesn't retrieve the info - it's just staying blank. The reference numbers that should be appearing, are not...?

[boopathiraja]

Outbound Call Data!$A$18:$A$7500[/COLOR],AGGREGATE(15,6,ROW(Outbound Call Data!$K$18:$K$7500)/(Outbound Call Data!$K$18:$K$7500=November 2014 Stats!$B$32)-1,ROWS($A$1:A1))),"")

Hi, Just seen your edited msg, -1 in this part shoud be -17, since you are reference starting from 18th row

ROW(OutboundCallData!$K$18:$K$7500)/(OutboundCallData!$K$18:$K$7500=November2014Stats!$B$32)-1

ROW(OutboundCallData!$K$18:$K$7500)/(OutboundCallData!$K$18:$K$7500=November2014Stats!$B$32)-17