Reference error in dynamic data with 1 criteria

[Timo1986]

Hi all,

I really hope you can help me with the following problem:

I have one table that gets its data dynamically from another table, based on one criteria (see the attached file). The criterium is stated in cell I2, the source data is stated in the columns A-G.

Based on the data in columns A-G, and the criterium in cell I2, I created a table in columns L-R. However, columns O, P and Q give an error message.

What am I doing wrong here? I have thought of every possible error that I could have made myself, but couldn't discover one.

Thanks in advance!!

Best regards,
Timo (from the Netherlands)

Dynamic_data.xlsx

[Paul]

Hi Timo,

The issue is that your named ranges for "Product Name", "Unit Price" and "In Stock" are actually "Product_Name", "Unit_Price" and "In_Stock". This is because you cannot have named ranges with spaces in them. So the easy way to fix is to change the column headers in O1, P1 and Q1 to include the underscores, or change your formulas to replace the spaces in the INDIRECT function to underscores. (I'd say the first option is easier.)

If you choose the second option, it would be this in cell O1 (and filled down and right into P and Q):

=IF(ROWS(O$2:O2)<=$J$2,INDEX(INDIRECT(SUBSTITUTE(O$1," ","_")),SMALL(IF(Category=$I$2,ROW(Category)-ROW($B$2)+1),ROWS(O$2:O2))),"")

[Richard Buttrey]

You're over complicating it with unnecessary array formulae.

In M2 copied across and down use
=INDEX(B:B,MATCH($L2,$A:$A,FALSE),1)

Of course a neater solution would be to use Data Filter Advanced in a small macro and attach it to a click 'button' - see below. I'd create range names for the hard coded ranges below and a dynamic range name for your A1:G70 data (and substitute these below) but hopefully you get the idea.

Please Login or Register  to view this content.

Regards

[Timo1986]

Hi Richard,

Thanks for the help.

Your formula above is partly based on the values in column L2. However, these values are also based on the rather complicated formula I used. Assuming column L is also empty, what would be your solution then?

@ Paul: perfect, the range names were the error indeed! Thanks!

Regards,
Timo

[Richard Buttrey]

Hi Richard,

Thanks for the help.

Your formula above is partly based on the values in column L2. However, these values are also based on the rather complicated formula I used. Assuming column L is also empty, what would be your solution then?

Regards,
Timo

My solution would be as advised before.
Personally I would always use Data Filter for something like this rather than resorting to formulae, and particularly array formulae which slow down calculations in large ranges, and even more when using a volatile function like INDIRECT(). Those are two good reasons for avoiding (in my humble opinion - YMMV) formulae in this case.

Regards