How to tie cell choice from dropdown list to content of other cell

Question

On a given range of cells, I have imposed the following data validation. The options come from a list contained in another sheet (Legends):

For each of these cells, I would like the option H to be selected whenever the corresponding cell in another sheet is non-empty. So I have tried:

in Sowing 20!O4=IF(Harvest 20!O4<>0,Legends!A10,"")

which gives error. Also the following does not work:

in Sowing 20 O4=IF(Harvest 20!O4<>0,"H","")

What's the correct way to achieve this?

Answer 1

I believe the error you are running into the space in the name of the sheet. Have you tried:

=IF('Harvest 20'!O4<>0,Legends!A10,"")

The space in the sheet's name is most likely the issue.