Formula result (using F9) is different than what is displayed in the cell

Very difficult to diagnose without seeing the workbook.
Any chance you could post a desensitized version?

BSB

Hey, thanks for reply BadlySpelledBuoy.

I've created a version where the specific names and job details have been removed and I've attached it here. It will take some explanation.

The spreadsheet is for keeping track of the location of staff members to assist with job scheduling. There is a "Data Entry" tab that takes the details of the staff members, the table containing team members, locations and groups is relevant, the other is not.

There is also a "Planning Tab" where the details of the all the jobs go. A staff member gets assigned to each job.

The "Task Monitoring" contains the table where I'm having the problem. I want to be able to select a staff member "group" from the drop down menu (just above the table) and have the details of the jobs that staff members in that group are assigned to display dynamically on this table. The troublesome formula is on the first few rows and first 7 columns of this table. You'll see the #NUM! errors but when you select one of those cells and F9 the formula it will display a result.

The two tabs "Hidden" and "HiddenWkly" contain references for the calculations, it should be clear what they're referencing when reading the formula. There is only 1 column on "HiddenWkly" that is being used in the calculcation, you'll see it.

I'm sorry to say that this spreadsheet was not created from scratch. I'm altering an existing one that was for a different purpose, so there is a lot of junk around. If I can help to explain anything further please let me know.

I can understand if this is tedious to go through, if I can make it easier any way or if what I've submitted is too messy please let me know.

Thank you kindly.

Have you tried using the evaluation tool to see which part of the whole formula fails?

Alt m v

Without seeing the workbook, or, at minimum the formula, we have nothing to go on that will help us identify the cause.

You have array formulas not confirmed as array formulas.

Select B17

Press f2

Hold down Shift and Ctrl together, then press Enter while you're holding them.

Use the fill handle to copy the formula down the table.

Repeat with the other columns.

The problem is these are array formulas but have not been confirmed properly. You need to press Ctrl, Shift & Enter to confirm them rather than just Enter.

You will know if it's been done properly because they will be surrounded by curly braces { }.

See attached.

BSB

Thanks BSB, that's exactly what I was missing. I'm still new to the more advanced aspects of Excel. I can go to bed happily now.

Thanks as well to the others who took the time to reply.

Kind regards.

Glad we could help

BSB