#VALUE! error

[Ginnie_H39]

Happy Monday everyone. I have a workbook for the employees in our dept that have all the pd time off information. I am trying to make a summary of the sick leave bonus. Each employee has individual sheets. I am doing = then clicking the cells that I want from the individual sheets to the summary sheet so it will automatically update. Most of the time it works.  I have 3 that comes up with #VALUE and I do not know what to do to correct it. I even copied an employees sheet that it did not error on and replace the information with the correct information thinking that would get rid of this error. It did not. Could someone tell me what I am doing wrong? Thank you.

[#2]

Unless I'm mistaken, you've posted real names of real people. That's a no-no on this site. Please remove that file; replace it with one without real names.
If in fact they're fictitious, then say so. But they certainly don't look it.

[#3]

@Ginnie_H39 

Since you have already posted it, and I've already looked at it, and have the following observation and solution)

Some of your tab names have a space after the name. (Downing is one; the tab itself said "Downing " and though your eyes and mine see that as the same as "Downing" Excel sees the space and sees it as different. Therefore the error message.

That said, I created a different formula to pull the dates in cell F39 from each of the individual tabs. It uses INDIRECT and the last name pulled from column A. You do NOT need to be copying and pasting every cell.

=INDIRECT(RIGHT(A2,LEN(A2)-FIND(" ",A2))&"!F39")

It would help if you created the list in column A differently. Surely you have a list of all employee names somewhere. As it is, you're pulling those too from the separate employee tabs. Not at all an efficient way to do it.

I'm returning a totally anonymous version with only one employee--newly hired--by the name of Darth Vader. You can copy the formula, though to your original sheet.  If you still have difficulty, by all means, come back, but post a copy of your workbook with anonymous names; Star Wars characters usually make it interesting.

[#4]

Thank you, I did not know about that name, I apologize for this mistake. I will look at what you gave me and see how it works. Thank you so much.