Filter function cell reference

[rrussell]

I have a block of data, each row has a different number of populated columns. Some cells were left blank, and some have more data than others. My problem is that the function I am using to analize this data doesn't like blank cells, is there a way to reference the cells to only use the populated fields under the full range of cells that could be filled in?

 

[Domenic]

Domenic's method will work for my situation but when I tried using it in another worksheet I had a problem with the INDIRECT function. For some reason it does not like the text part "1:" (result is #value). Any idea why it was working in one worksheet, but not the other. The cells seam to be formated the same.

Are you still using defined references for the second sheet or have you dispensed with them and using them in the formula itself? If so, you'll have to confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER.

Also, following Aladin's lead, my solution can be modified to allow an unequal number of empty cells...

1) Add the following defined reference...

Name: Num

Refers to: =MIN(COUNT(Sheet1!$D$15:$K$15),COUNT(Sheet1!$D$14:$K$14))

Change the following defined references...

Array1:

=SUBTOTAL(9,OFFSET(Sheet1!$D$15:$K$15,,SMALL(IF(ISNUMBER(Sheet1!$D$15:$K$15)*ISNUMBER(Sheet1!$D$14:$K$14),COLUMN(Sheet1!$D$15:$K$15)-COLUMN(Sheet1!$D$15)),ROW(INDIRECT("1:"&Num))),,1))

Array2:

=SUBTOTAL(9,OFFSET(Sheet1!$D$14:$K$14,,SMALL(IF(ISNUMBER(Sheet1!$D$15:$K$15)*ISNUMBER(Sheet1!$D$14:$K$14),COLUMN(Sheet1!$D$14:$K$14)-COLUMN(Sheet1!$D$14)),ROW(INDIRECT("1:"&Num))),,1))

Hope this helps!

 

[Aladin Akyurek]

...

Also, following Aladin's lead, my solution can be modified to allow an unequal number of empty cells...

1) Add the following defined reference...

Name: Num

Refers to: =MIN(COUNT(Sheet1!$D$15:$K$15),COUNT(Sheet1!$D$14:$K$14))

...

Expensive though. And you have to replace that MIN formula with the SUMPRODUCT formula I mentioned in my last post.

 

[Domenic]

...

Also, following Aladin's lead, my solution can be modified to allow an unequal number of empty cells...

1) Add the following defined reference...

Name: Num

Refers to: =MIN(COUNT(Sheet1!$D$15:$K$15),COUNT(Sheet1!$D$14:$K$14))

...

Expensive though. And you have to replace that MIN formula with the SUMPRODUCT formula I mentioned in my last post.

Thanks Aladin! I just read your last post and realized that I would have to do the same. So, to be clear for the OP's sake, the reference for Num should be changed to...

=SUMPRODUCT(--ISNUMBER(Sheet1!$D$14:$K$14),--ISNUMBER(Sheet1!$D$15:$K$15))

But yes, rather expensive...

 

[Domenic]

Aladin, quick question...

If a reference is defined for each part of the formula...

SUMPRODUCT()

ROW(INDIRECT(...))

SMALL()

OFFSET()

SUBTOTAL()

...would it be as efficient, or at least nearly as efficient as the solution you offered?

 

[Domenic]

Domenic's method will work for my situation ...

Actually, the first solution I offered needs to be corrected. Note that the correction won't address the error that you're experiencing. If you choose to stick with the first one, or continue to have problems, post back.

 

[rrussell]

Domenic's method will work for my situation ...

Actually, the first solution I offered needs to be corrected. Note that the correction won't address the error that you're experiencing. If you choose to stick with the first one, or continue to have problems, post back.

I would like to work with your first solution if the INDIRECT function can be corrected. If it works I will be able to apply it to a number of different data tables.

 

[Domenic]

I would like to work with your first solution if the INDIRECT function can be corrected. If it works I will be able to apply it to a number of different data tables.

Okay, just to clarify...

1) Do you want to exclude 0 values?

2) Are you still using defined references for your other sheet, or have you dispensed with them and using them in the formula directly?

 

[rrussell]

I still need to exclude 0 and blank entries. I have built the references into the formula so I would be able to copy it into different tables without having to define new references.

 

[Domenic]

I still need to exclude 0 and blank entries. I have built the references into the formula so I would be able to copy it into different tables without having to define new references.

As I suspected, the reason you're getting a #VALUE! error is that you've dispensed with the defined references. In this case, the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

So, assuming that for each cell in one row that contains a 0 value, the corresponding value in the other row also contains a 0, try...

=LINEST(SUBTOTAL(9,OFFSET(D15:K15,,SMALL(IF(ISNUMBER(D15:K15)*(D15:K15>0),COLUMN(D15:K15)-COLUMN(D15)),ROW(INDIRECT("1:"&COUNTIF(D15:K15,">0")))),,1)),SUBTOTAL(9,OFFSET(D14:K14,,SMALL(IF(ISNUMBER(D14:K14)*(D14:K14>0),COLUMN(D14:K14)-COLUMN(D14)),ROW(INDIRECT("1:"&COUNTIF(D14:K14,">0")))),,1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[rrussell]

This is pretty much the same as I have been trying. I tried again with your updated comand and have the same problem. I can see the #value error is only present in the INDIRECT part of the command. I have been playing with INDIRECT on it's own and it doesn't want to accept the text portion combined with the countif function. It is alright with either ontheir own, but fails when combined.

I stumped because you command worked perfectly on a worksheet i was using as a test. It has the same layout and format and is in the same workbook.