Annoying message "Excel found a problem with one or more formula...."

[tico_ocit]

Hi there,
PLEASE HELP!!!!

I'm desperate with this annoying error messaing!


Can I make it not to show? Application.displayalerts=False , is not working!
I have named ranges, but of course, if the sheet has no data, the ranges and the graphs attached to that no work. But I know that!
I just don't want to appear!!!

Can someone tell me a work around to it?

Thank you in advance!

 

[RAJESH NATH]

can you provide your sheet

 

[jasonb75]

If DisplayAlerts = False is not doing the trick then I doubt that it can be suppressed.

Maybe some changes to your named range definitions so that they refer to an empty cell when there is no data instead of creating errors?

 

[tico_ocit]

can you provide your sheet

I really can't sorry.

If DisplayAlerts = False is not doing the trick then I doubt that it can be suppressed.

Maybe some changes to your named range definitions so that they refer to an empty cell when there is no data instead of creating errors?

Do you have any suggestions? I think what I'll need to do, is populate the ranges with something, like "Sample", maybe it might work, no? I didn't already do that because there is massive macro that depends on the data.

 

[jasonb75]

Not without seeing the formulas used to define the ranges. Assuming that MATCH is involved, you could error trap it to a default value of 1.

 

[tico_ocit]

Not without seeing the formulas used to define the ranges. Assuming that MATCH is involved, you could error trap it to a default value of 1.

Ok, here an example: =OFFSET(Global!$B$5,MATCH("Compras",Global!$B$5:$B$24,0)-1,Global!$V$5-2,,Global!$V$6)
Note:
- Global V5 - first column that contains values - where starts the area
- Global V6 - number of months - The width of area.

I have this table,
Compras - Shopping and Hipermercado - Hypermarket, it's related to expenses.
I have a graph, that only appears the months that have values.

The values of the table came automatically from another sheet.

Thank you in advance!

 

[jasonb75]

Based on that, the match part is going to cause an error if 'Compras' is not found, the other parts shouldn't cause errors as long as the cells referred to are either numeric or empty (blank will probably cause an error). Changing it as below should eliminate the error for this range.

=OFFSET(Global!$B$5,IFERROR(MATCH("Compras",Global!$B$5:$B$24,0)-1,1),Global!$V$5-2,,Global!$V$6)

You should be able to apply similar theory to other ranges.

 

[tico_ocit]

Based on that, the match part is going to cause an error if 'Compras' is not found, the other parts shouldn't cause errors as long as the cells referred to are either numeric or empty (blank will probably cause an error). Changing it as below should eliminate the error for this range.

=OFFSET(Global!$B$5,IFERROR(MATCH("Compras",Global!$B$5:$B$24,0)-1,1),Global!$V$5-2,,Global!$V$6)

You should be able to apply similar theory to other ranges.

It's not working, because, I have named tables too...
I going for populating the data with some sample as examples, and when run macros, clear the cells. I think it's better, otherwise I'll maybe get insane. :P

 

[jasonb75]

I going for populating the data with some sample as examples, and when run macros, clear the cells. I think it's better, otherwise I'll maybe get insane. :P

Sometimes the simple way is the safest.

 

[tico_ocit]

Sometimes the simple way is the safest.

I get it! Like this:
=OFFSET(Global!$B$5,MATCH("Compras",Global!$B$5:$B$24,0)-1,Global!$V$5-2,,IF(Global!$V$6=0,1,))
The problem was with the width of the offset. If there is no data, Global!$V$6=0, making returning the error!
Thank you @jasonb75 ! You were really helpful!