It appears to me that it is not possible to create a chart on one worksheet which is based upon data sourced from another worksheet in the workbook. Am I correct?
Apparently not - see the attached file
chart_on_other_sheet.ods
If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.