Removing Err:502 in blank cells in Libreoffice calc

Hi all. I have created a spreadsheet that works fine as a project planning tool at a basic level. I have used examples from other experts & online sources and made my own additions. At this point, it works fine as is, It consists of 1 ‘Details’ sheet and 1 ‘Gantt’ sheet. I want to re-use it and share it with family & friends who are asking for it. My issue is simple: Many cells rely on many cells, and if the initial cell does not contain data, then my Gantt sheet is filled with Err:502. I have plenty of conditional formatting and have tried several diffirent potential fixed, but nothing works and anymore fixing will break my sheet I am sure. Would anyone be able to have a look at my document and see if they can point out what I need to do where? Sheets are protected with no password to keep it clean and fault free during work. The initial requirement is the date entry in the ‘Details’ sheet for Date notified, where the Day, Month & Year are inserted by way of drop downs. If this is filled, there are no Err:502 issues in the Gantt sheet
MASTER_MAKE_COPY_Project_plan.ods (345.0 KB)

Have made some changes and added comments so you know what’s been changed. Effectively I’ve enclosed the formulas in the cells with errors with IFERROR statements that display nothing if there is an error. Not necessarily the best practice as you can’t then see errors but acceptable if you understand how to diagnose your workbook.

Have left some for you to do :wink:

MASTER_MAKE_COPY_Project_plan - Ash733 Changes 20240711.ods (343.8 KB)

1 Like

Thank you for the fix. I knew it was the IFERROR statement, but I am not mostly successful at applying.

Just a note, on the Gantt sheet, you removed the DATEVALUE function, but I added this because the first row of the Gantt calendar does not work without it. I managed to refer directly to Details sheet date cell E5 which was CONCATENATED by changing the formula in E5 to: =IF(OR(ISBLANK(B5), ISBLANK(C5), ISBLANK(D5)), “”, DATEVALUE(CONCATENATE(B5, " ", C5, " ", D5)))

I have also removed the #VALUE! in my Gantt calendar by applying your recommendation after some searches and CHATGPT. The formula is now: =IFERROR(IF(AND(E$5>=$B6, E$5<=$D6, WEEKDAY(E$5, 2) <= 5, ISNA(MATCH(E$5, $NZ$6:$NZ$26, 0))), " ", “”), “”)

I am uploading the ‘completed’ sheet here now if you or anyone else wants to see the changes (I made notes). It is now where I wanted it to be. Anything from this point is evolution.
MASTER_MAKE_COPY_Project_plan - Ash733 Changes 20240711_updated.ods (356.2 KB)

1 Like

Details:B7 and E6 are binded in an error

Hi xerostomus, can you provide some more details? Details B7 needs to have the IFERROR function to avoid the #VALUE! result, and E6 is just a formality I copied from E5 above for sanity. Do you think these should be different?

You must go backwards:

Days to open #VALUE! — on Details tab
=E6 - today()

What is on E6?
Concatenate E6
So it goes in a circle, so Error…

You must go backwards and solve all errors on in the sheet.

The second error:
The Cell Gant!B3 must be
I guess…