ignoring blank cells in an array forumla

[ENAPo]

I have this formula in a cell:
=IF(ISERROR(INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5)),"",INDEX($R$1:$AF$5000,SMALL(IF($R$1:$AF$5000=$Q$1,ROW($R$1:$AF$5000)),ROW(5:5)),5))

If the cell it's pulling the information from is blank it puts a 0 in the cell. I want it to just leave it blank if the cell is blank but am at a loss where to put that in the formula. Hope that doesn't sound confusing.

Thanks!!

 

[Aladin Akyurek]

It is super secret.

6	10/31/2013	14578	Butter County
3	10/16/2013	15489	Cream County
2	10/6/2013	45879	Cheese County
6	11/02/2013	45684	Butter County

<TBODY>
</TBODY>

This is what I need to do and I hope I explain it right. I have a large amount of information with 15 columns and up to 2000+ rows, that I paste/import into Excel from Quickbooks. I have a drop down box that lists products that coincides with the numbers in the above table. (6,3...). When I select a product from the drop down menu, I want it to populate all the information in my 2000+ rows that equals the number into another section of the worksheet. Ideally, I would like a new worksheet separate from the main information. For instance, #6 is populated in cell Q1 when I select the product from the drop down menu. So in another worksheet or area of table, I want everything in that row that equals 6 to be populated and however many rows has 6. Does that make any sense? I basically want a whole other table that filters for only the item that I have selected. I need to show this to others and I would rather not have them mess with filtering. I would be REALLY nice if I can have it filter based on the month that is populated in A1 as well. For instance, only give me every line that equals 6 and is in the month of October. I am really new to all these formulas in excel and am trying my best. Not trying to be aloof.

Sheet1, A:D, houses the relevant data.

Product	Date	Code	Location
6	10/31/2013	14578	Butter County
3	10/16/2013	15489	Cream County
2	10/6/2013	45879	Cheese County
6	11/2/2013	45684	Butter County
2	11/21/2013	45953	Cheese County
6	11/21/2013	45785	Butter County

<TBODY>
</TBODY>

Sheet2, A:E, houses the processing...

Nov
6
Idx	Product	Date	Code	Location
4	6	41580	45684	Butter County
6	6	41599	45785	Butter County

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3157" width=89><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3271" width=92><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6144" width=173><TBODY>
</TBODY>

A1: Mar (a month of interest, given as a literal month name)

A2: 6 (a product of interest, selected from the dropdown list in this cell)

A5, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(Sheet1!$A$2:$A$7=$A$2,
  IF(Sheet1!$B$2:$B$7-DAY(Sheet1!$B$2:$B$7)+1=(1&$A$1)+0,
  ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2)+1)),ROWS($A$5:A5)),"")

B5, just enter, copy across as far as needed, and down:

=IF($A5="","",INDEX(Sheet1!A$2:A$7,$A5))

Note 1. If so desired, A1 can house a true date like 1-Nov-13, custom-formatted either Nov-13 or Nov.
Note 2. If so desired, the references to the data area can be set up as references to dynamic named ranges.

 

[markmzz]

Sorry - I think I get it now .
I put my own data into the cells you've used in order to demonstrate the different results?. I'm afraid I'm good at missing the obvious sometimes..........

Thanks very much for your help.

Hercules

You're welcome.

Markmzz

 

[markmzz]

Another way:

Layout

Header01	Header02	Header03	Header000004	Header05	Header06	Header07	Header08	Header09	Header10	Header11	Header12	Header13	Header14	Header15	Sheet1
6	31/10/2013	14578	Butter County	49	49	29	33	95	46	15	56	29	27	73
3	16/10/2013	15489	Cream County		26	75	84		42		91	22	59
2	06/10/2013	45879	Cheese County	11	19	93	57	93	10	68	40	64	81	22
6	06/10/2013	45684	Cheese County	56		53	18	35		72	93	99		78
6	16/10/2013	14578		86	58	87	80	36	67	97	49	23	93	27
3	16/10/2013	15489	Cream County	80	59	59	96	82	94	40	46	23	77	94
2	06/10/2013	45879	Cheese County	91	49	69	44	65		93	85	59	70
6	02/11/2013	45684	Butter County	91	12	30	80	23	88	40	46	56	67	76
3	30/10/2013	43711			59	90	66	28	64	51	16	12	73	85

<colgroup><col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" span="11"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <tbody>
</tbody>

Header01	Header02	Header03	Header000004	Header05	Header06	Header07	Header08	Header09	Header10	Header11	Header12	Header13	Header14	Header15	Sheet2
6	10/2013	14578	Butter County	49	49	29	33	95	46	15	56	29	27	73
		45684	Cheese County	56		53	18	35		72	93	99		78
		14578		86	58	87	80	36	67	97	49	23	93	27
		37029	Cheese County	91	22	59	28	50	86	71	60	36	21	24
		29870		77	88	40	13	88	67	36	82	80	91	13
		53955	Cheese County	27	74	61	93	35	43	12	20	40	37	16
		25052	Cream County	93	66	11	69	98	95	83	81	75	34	24
		34201		37	45	61	86	69	13	81	82	13	21	49
		30406	Cheese County		36	44	90	60	89	55	26	96	58	60

<colgroup><col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" span="3"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" span="11"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <tbody>
</tbody>

Formula

In C2 (in Sheet2) - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(IF(Sheet1!C$2:C$2500="","",Sheet1!C$2:C$2500),
SMALL(IF(Sheet1!$A$2:$A$2500=$A$2,IF(TEXT(Sheet1!$B$2:$B$2500,"mmyy")=
TEXT($B$2,"mmyy"),ROW(Sheet1!$A$2:$A$2500)-ROW(Sheet1!$A$2)+1)),ROWS(C$2:C2))),"")

And copy to the right and down.

Markmzz