Help in IFERROR()

[shivya]

Hello Experts,

I am trying to create a dashboard in excel.
I want to display all the data on my sheet first, so,
I am getting an error of #NA as there is no data available.
it's okay but
i want " " instead of #NA.
For this, I used iferror() of IFNA() but not working, please check
find the attached sheet

[BadlySpelledBuoy]

Please Login or Register  to view this content.

This should work, but I think it very much depends on what exactly do you mean by "not working"?

That is a lengthy formula that could be greatly simplified if the data were laid out differently.
Is there any reason the data is in that configuration?

BSB

[chief_abound]

I Think...

It is better to use the INDIRECT function after defining the range for each product.

[shivya]

@BadlySpelledBuoy,

iferror() is not working, that is the issue.
And, data should be there because i have to create chart on

[shivya]

I Think...

It is better to use the INDIRECT function after defining the range for each product.

How to use indirect here?

[Richard Buttrey]

That formula is crazy and difficult to maintain when you add new items.

You'd be much better to change the way your product list is laid out and create a lookup table which contained all permutations of the strings. The first column would concatenate the three basic type strings of non_branded, flood light and bridge plus the description and every column heading of the product list into as many permutations of those strings that you have. Then in the second column of the lookup table enter the value or measurement of that permutation.

[JohnTopley]

Why not (at least) have all the "Branded" and "Non-Branded" tables together, with the product name e.g."Flood light" in either the "heading" row column A Or first data row. Plus, as already suggested, consider using named ranges.

And don't see why such an arrangement would impact of producing charts or any other function.

EDIT: Richard offers another approach as well.

[shivya]

yes Richard,

any other formula is possible with the conditions?

[JohnTopley]

See attached:

I have minimised the changes to your price tables (although further changes are possible by having a "Branded" and "Non-Branded" table to reduce headings etc).

Named ranges in tab "Tables" for "Branded" and "Non Branded"

in "Dashboard x"

in F7

=INDEX('Product Price List x'!$B$5:$R$200,MATCH('Dashboard X'!$E$8,'Product Price List x'!$A$5:$A$200,0)-1,COLUMNS($B:B))

copy across to S

in F8

=IF(ROWS($1:1)<=COUNTIF('Product Price List x'!$A$5:$A$200,'Dashboard X'!$E$8),INDEX('Product Price List x'!$B$5:$R$200,MATCH('Dashboard X'!$E$8,'Product Price List x'!$A$5:$A$200,0)+ROWS($1:1)-1,COLUMNS($B:B)),"")

Copy across and down

format F7:R100 as Custom ";;;@" (without quotes)

[chief_abound]

deleted an existing name definition.

I created a list of brands on the List sheet.

--> This list is for use on Dashboard!E8.

For convenience, we have defined four names and created the following formula.

F7:T21

--> ={IFERROR(INDIRECT(SUBSTITUTE(SUBSTITUTE(E8,"/ ","")," ","_")),"")}

The list is as follows.

TUBE LIGHT
Corn Light
Panel Light Round / Square back Light
Bridge Street Light

Choose one of the four lists on E8.

If it helps...try making the other names yourself.

I used conditional formatting to hide errors.

[shivya]

Worked for me. Thanks to John and Chief. added reputation as well.