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

tico_ocit

Board Regular
Joined
Apr 5, 2019
Messages
95
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!
 

Attachments

  • Excel_Error.png
    Excel_Error.png
    3 KB · Views: 20
Strange, I wasn't getting errors with 0 width.

Might be better to use MAX(Global!$V$6,1) though, the empty FALSE field in IF will still result in an error if V6 is anything other than 0 or 1.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Strange, I wasn't getting errors with 0 width.

Might be better to use MAX(Global!$V$6,1) though, the empty FALSE field in IF will still result in an error if V6 is anything other than 0 or 1.

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

You're right!
Global!&V5& -> =IFERROR(COLUMN(INDEX(INDIRECT("C"&V4&":N"&V4),,MATCH(TRUE,INDEX((INDIRECT("C"&V4&":N"&V4)<>0),0),0))),3)
V4 -> Return the line
So if there aren't any values, GlobalV5 return N/D. So, based on your suggestion I added IFERROR, giving value 3, but I could give any value...

The MAX function ignores errors?
Global!&V6& -> =COUNTIF(INDIRECT("$C$"&$V$4&":$N$"&$V$4,TRUE),"<>"&0)
Is the number of months with values.
If there are no data, it returns 0.
Even so, do you recommend using MAX?

Thank you for your time!

[EDIT]: I have to enter Global!$V6$ on IF, if it is false. Represented on italic.
 

Attachments

  • excel_table_example.jpg
    excel_table_example.jpg
    143.6 KB · Views: 5
Last edited:
Upvote 0
With the part in italic that you added to the formula, either way will work fine. I suggested max because it was a shorter formula, but other than that there is no advantage to using either method.

That said, if I'm following correctly then there are other parts that I would change, but I need to be sure that I'm correct before suggesting changes.

Based on the example, I'm seeing that the range for Compras should return the figures for July and August, but what if the figures in the table were July 300, August 0, September 200?

Your existing formulas would still return the range for July and August, the V6 formula would not see September in this example, or any other area where there are months with zero between months with non zero values.

Also, what is in V4? Is that the name (Compras, etc) as well, or something else?
 
Upvote 0
With the part in italic that you added to the formula, either way will work fine. I suggested max because it was a shorter formula, but other than that there is no advantage to using either method.

That said, if I'm following correctly then there are other parts that I would change, but I need to be sure that I'm correct before suggesting changes.

Based on the example, I'm seeing that the range for Compras should return the figures for July and August, but what if the figures in the table were July 300, August 0, September 200?

Your existing formulas would still return the range for July and August, the V6 formula would not see September in this example, or any other area where there are months with zero between months with non zero values.

Also, what is in V4? Is that the name (Compras, etc) as well, or something else?

I August doesn't have any value, I have a problem. Because, these named ranges are for create a "dynamic" chart, this means, if August is 0, it will =COUNTIF(INDIRECT("$C$"&$V$4&":$N$"&$V$4,TRUE),"<>"&0) only 2, which means that in the chart will appear July (because is the first column that has a value <> 0, and the width is 2, July and August.

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

Where I have "Compras" - "Shopping" in english, the truth is that it is populated based on a data validation. If I want to see the chart about "Hipermercado" - "Hipermarket", I would choose it.
Am I making myself clear? ;)

[EDIT]
Since you are helping, I'm struggling with something. The excel doesn't like very much that you hide all the columns at your right, to make a more "software" like environment. As you can see one image, I hid all the columns from column R to last column available on the sheet.
Do you have anything that can't work around this thing?
 

Attachments

  • excel_table_sheet.jpg
    excel_table_sheet.jpg
    200.2 KB · Views: 5
Last edited:
Upvote 0
Trying to fix one part at a time so that it doesn't get too complicated, the formulas below use a different method to find the first and last non zero values in the specified row.

Global!V5 > =IFERROR(AGGREGATE(15,6,COLUMN($C$1:$N$1)/(INDEX($C:$N,V4,0)<>0),1)-COLUMN($C$1),0)
Global!V6 > =IFERROR(AGGREGATE(14,6,COLUMN($C$1:$N$1)/(INDEX($C:$N,V4,0)<>0),1)-COLUMN($C$1)-V5+1,1)

Named range definition> =OFFSET(Global!$C$4,IFERROR(MATCH("Compras",Global!$B$5:$B$24,0),1),Global!$V$5,1,Global!$V$6)

I think that should do what you need. If there are no non zero values then it will default to column C (single zero value cell). If there is no match for the 'Compras' dropdown then it will default to row 5, meaning that the named range will default to $C$5 on an empty sheet.

I'm not sure exactly what you're trying to do in regards to the edit that you added to your last post, it would be better to start a new thread for that, although I expect that whatever it is that you're trying, most will advise against it if it is causing issues.
 
Upvote 0
Trying to fix one part at a time so that it doesn't get too complicated, the formulas below use a different method to find the first and last non zero values in the specified row.

Global!V5 > =IFERROR(AGGREGATE(15,6,COLUMN($C$1:$N$1)/(INDEX($C:$N,V4,0)<>0),1)-COLUMN($C$1),0)
Global!V6 > =IFERROR(AGGREGATE(14,6,COLUMN($C$1:$N$1)/(INDEX($C:$N,V4,0)<>0),1)-COLUMN($C$1)-V5+1,1)

Named range definition> =OFFSET(Global!$C$4,IFERROR(MATCH("Compras",Global!$B$5:$B$24,0),1),Global!$V$5,1,Global!$V$6)

I think that should do what you need. If there are no non zero values then it will default to column C (single zero value cell). If there is no match for the 'Compras' dropdown then it will default to row 5, meaning that the named range will default to $C$5 on an empty sheet.

I'm not sure exactly what you're trying to do in regards to the edit that you added to your last post, it would be better to start a new thread for that, although I expect that whatever it is that you're trying, most will advise against it if it is causing issues.

It's working, although I have to delete the bold parts. But I have a doubt, if you can help me would be great. COLUMN($C$1:$N$1)/(INDEX($C:$N,V4,0)<>0 , what this means? Can I use <>0 on a INDEX?
Thank you!
Regarding the last subject of my previous post, I'll make a new thread.
 
Upvote 0
The part that you ask about creates an array of column numbers, <>0 is a logical test of the range returned by index. COLUMN($C$1:$N$1)/(INDEX($C:$N,V4,0)<>0) would generate the array, COLUMN({3,4,5,6,7,8,9,10,11,12,13,14})/({0,0,1,2,0,1,5,0,0,0,0,0}<>0) where the second array (in bold) shows the values in the row specified in V4 of columns C:N.

The <>0 test at the end results in any 0 values equating to FALSE or non 0 values equating to TRUE. Dividing the column numbers by TRUE of FALSE will result in an array containing numbers and errors, from the above example the final result would be {#DIV/0!,#DIV/0!,5,6,#DIV/0!,8,9,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0,!}.

The aggregate function ignores the errors and returns either the lowest, AGGREGATE(15,6,...) or the highest, AGGREGATE(14,6...) column number in the array.

You do need the parts in bold that you said you had to delete though, otherwise the resulting range will be out significantly, the numbers in V5 and V6 are not actual column numbers, they are position references relative to column C.

V5 should be the difference between column C and the first non zero data column, for example if the first value is in column C then V5 should show 0, not 3, as would be shown with the bold part deleted.

V6 should be the number of columns to be included in the range, for example with non zero values in columns C, E and G, the formula will show 5 (C,D,E,F and G), with the bold part removed from this formula it will incorrectly show 7.

Based on the above, the resulting range should be C5:G5 but with the bold parts removed and the column numbers in the cells instead of the corrected positions the actual range returned will be F5:L5 which is missing most of the actual data.
 
Upvote 0
Thank you for the explanation! I didn't know that I could use a condition to build an array! Aggregate is good, because avoid returning error or 0.

V5 I'm using as the column for the first month, but no the column of the table, but the column of the sheet, is what you refering?
 
Upvote 0
V5 I'm using as the column for the first month, but no the column of the table, but the column of the sheet, is what you refering?
The COLUMN($C$1:$N$1) part of the formula refers to the column of the sheet (C=3, D=4, E=5, etc) but offset needs the column of the table starting from 0, (C=0, D=1, E=2, etc) so the first column of the table needs to be subtracted from the result for it to work.
 
Upvote 0
I was too slow to edit my reply above, hopefully this will help with the explanation.
Book1
BCDEFGHIJKLMNOVW
301234567891011
4JanFebMarAprMayJunJulAugSepOctNovDec5Row of sheet
5Compras0000581089231100004Column based on numbers in Row 3
64Count of months from first to last non zero.
71784Sum using offset formula.
Sheet15
Cell Formulas
RangeFormula
V4V4=MATCH("Compras",B:B,0)
V5V5=IFERROR(AGGREGATE(15,6,COLUMN($C$4:$N$4)/(INDEX($C:$N,V4,0)<>0),1)-COLUMN($C$4),0)
V6V6=IFERROR(AGGREGATE(14,6,COLUMN($C$4:$N$4)/(INDEX($C:$N,V4,0)<>0),1)-COLUMN($C$4)-V5+1,1)
V7V7=SUM(OFFSET($C$4,IFERROR(MATCH("Compras",$B$5:$B$24,0),1),$V$5,1,$V$6))
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top