#NUM! Error with Extracting Data Based on Criteria

**ACM2** · 06-27-2012, 11:00 AM

I've been using a spreadsheet template which I found on YouTube to try and do a similar thing with my own workbook. http://www.youtube.com/watch?v=6bGKhbUYOas

The problem is (see the screenshot at the bottom), on the far left column, the top row is fine, it is pulling in the data housed in another worksheet. However, when I drag it down to the other cells, it does not display any data, it shows a #NUM! error instead. There should be 84 records which match the criteria "Arsenal Aston Villa League". It is only displaying one of the results, with the subsequent rows displaying a #NUM! error.

Here's the formula for the second cell of the far left column:

46098048.jpg

As you can see, the variable 'J17' changes +1 for each cell, yet only the top one works.

Any help appreciated. Thanks.

76165835.jpg

**bonny24tycoon** · 06-27-2012, 11:41 AM

Hi There,

We would not be able to examine or work on a jpg image.. Instead can you upload an example workbook?

**ACM2** · 06-27-2012, 12:49 PM

Yes, here is the example workbook.

example.zip

**Søren Larsen** · 06-27-2012, 01:25 PM

You need to confirm the formula using Ctrl+Shift+Enter and not just Enter.

**ACM2** · 06-27-2012, 02:38 PM

Originally Posted by Søren Larsen

You need to confirm the formula using Ctrl+Shift+Enter and not just Enter.

That works, thanks.

**Søren Larsen** · 06-27-2012, 02:44 PM

That's good!

**ACM2** · 06-27-2012, 07:05 PM

Just a minor thing which I'd like to fix. Currently, any extracted data coming from a blank cell will display "0". Now I know you can enter a "'" into that blank cell, but that isn't practical for so many. How can I adjust this formula so it will NOT display a 0 when the cell in the remote worksheet is blank?

=IF(ROWS(G$21:G21)<=$A$11,INDEX(INDIRECT(G$18),SMALL(IF(Dep=$A$19,ROW(Dep)-ROW('Season Results History (Hidden)'!$DC$4)+1),ROWS(G$21:G21))),"")

**Søren Larsen** · 06-28-2012, 03:39 AM

You probably have to do something like this:

Formula:

=IF(ROWS(G$21:G21)<=$A$11,IF(INDEX(INDIRECT(G$18),SMALL(IF(Dep=$A$19,ROW(Dep)-ROW('Season Results History (Hidden)'!$DC$4)+1),ROWS(G$21:G21)))=0,"",INDEX(INDIRECT(G$18),SMALL(IF(Dep=$A$19,ROW(Dep)-ROW('Season Results History (Hidden)'!$DC$4)+1),ROWS(G$21:G21)))),"")

**ACM2** · 06-28-2012, 06:34 AM

Originally Posted by Søren Larsen

You probably have to do something like this:

Formula:

=IF(ROWS(G$21:G21)<=$A$11,IF(INDEX(INDIRECT(G$18),SMALL(IF(Dep=$A$19,ROW(Dep)-ROW('Season Results History (Hidden)'!$DC$4)+1),ROWS(G$21:G21)))=0,"",INDEX(INDIRECT(G$18),SMALL(IF(Dep=$A$19,ROW(Dep)-ROW('Season Results History (Hidden)'!$DC$4)+1),ROWS(G$21:G21)))),"")

That works perfectly. Thank you!

What I've been getting since I implemented the array formulas is a lot of lag when I change a drop down box, it takes 5-6 seconds for the table to change sometimes, stating "Calculating (4 processors) 1%". Is there a way of quickening things up?

**Søren Larsen** · 06-28-2012, 06:53 AM

Yes, it's because of all the complex formulas. If you want to quicken things up your either need to adapt a new structure of your workbook, adapt a different approach to the formulas used or implemet macros to do the work instead. Perhaps you should change the workbook to something simpler and less dynamic.

**ACM2** · 06-28-2012, 07:08 AM

Originally Posted by Søren Larsen

Yes, it's because of all the complex formulas. If you want to quicken things up your either need to adapt a new structure of your workbook, adapt a different approach to the formulas used or implemet macros to do the work instead. Perhaps you should change the workbook to something simpler and less dynamic.

Would using named ranges instead of cell references help to any degree?

**Søren Larsen** · 06-28-2012, 08:29 AM

No, I shouldn't think so.

**ACM2** · 06-28-2012, 01:28 PM

Originally Posted by Søren Larsen

No, I shouldn't think so.

I read on here that using Excel's database functions are quicker than using arrays, because arrays are only ideal for small data sets. Would using the DGET function be quicker?

**Søren Larsen** · 06-28-2012, 01:54 PM

I took a look at your workbook, but I find it difficult to give any suggestions as long as you have all these ref/num errors. Do you have a working workbook we could see?

**ACM2** · 06-28-2012, 02:28 PM

Originally Posted by Søren Larsen

I took a look at your workbook, but I find it difficult to give any suggestions as long as you have all these ref/num errors. Do you have a working workbook we could see?

Yes, but as it is to be a commercial product, I don't wish to put the whole of the workbook on here (it is also 42MB in size). What I can do though is find another means of sending it to you.

**Søren Larsen** · 06-28-2012, 02:38 PM

No, that won't be nescessary. The reason I asked was that when looking at your workbook, I couldn't see why it should be that slow. But if your attachment isn't the entire project, then that's probably the reason. I would suggest that you close this thread (mark it solved), and create a new thread where you address this new issue of changing your project into something based on a database. Sadly I'm no expert in this area, so I think you'll get better help if you start a new thread.

#NUM! Error with Extracting Data Based on Criteria

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

#NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Re: #NUM! Error with Extracting Data Based on Criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions